June 16, 2015 at 11:53 pm
Comments posted to this topic are about the item Not so obvious COALESCE behaviour
June 17, 2015 at 1:01 am
Now the answer is NEVER.
Without the RTRIM then the ansewer : IF NULL is right.
It surprished me a bit when I use this code :
DECLARE @anyDateTime date = null
IF (COALESCE(@anyDateTime,'')) = ''
print 'true'
else
print 'false'
print '*' + cast(@anydatetime as varchar) + '*'
print COALESCE(@anyDateTime,'')
print rtrim(COALESCE(@anyDateTime,''))
MessagesPane:
true
1900-01-01
1900-01-01
Why true ? The COALESCE(@anyDateTime,'') returns 1900-01-01 which is not ''
June 17, 2015 at 1:50 am
ArnoKwetters (6/17/2015)
Now the answer is NEVER.Without the RTRIM then the ansewer : IF NULL is right.
It surprished me a bit when I use this code :
DECLARE @anyDateTime date = null
IF (COALESCE(@anyDateTime,'')) = ''
print 'true'
else
print 'false'
print '*' + cast(@anydatetime as varchar) + '*'
print COALESCE(@anyDateTime,'')
print rtrim(COALESCE(@anyDateTime,''))
MessagesPane:
true
1900-01-01
1900-01-01
Why true ? The COALESCE(@anyDateTime,'') returns 1900-01-01 which is not ''
There is already a fairly good explanation in the answer of the QotD. COALESCE (like many functions and operators in SQL Server), when confronted with mixed data types in the input, will implicitly convert values to be of the same data type and then return that data type. To determine which data type is used, the rules of data type precedence (https://msdn.microsoft.com/en-us/library/ms190309.aspx) are used. In the case of this example, the input data types are date and varchar(1). Date has a higher precedence, so the empty string is implicitly converted to date. This conversion results in Jan 1st, 1900, so the COALESCE returns a date value that is either @anyDateTime or, if that is NULL, Jan 1st, 1900.
In the QotD, the next step is RTRIM. This is a specific string function, so it will always return a string and convert its input to string. Jan 1st 1900 gets casted as string, resulting in '1900-01-01' or (depending on whether the data type used is date or datetime, and on the language settings) some other representation of that date. If @anyDateTime is not null, it will return a similar representation of whatever date is in that variable. Bottom line is that this is never equal to the empty string.
In your example, you omit the RTRIM. So the result of COALESCE (which was a date value) is compared to '' (a varchar(1) value). SQL Server again consults the rules of data type precedence and then implicitly converts the empty string to a date - again resulting in Jan 1st, 1900. Which makes the comparison true.
Interesting other queries to run:
SET LANGUAGE English;
DECLARE @anyDateTime date = NULL;
IF COALESCE(@anyDateTime, '1/1/1900') = '' PRINT 'Equal'; ELSE PRINT 'Different';
IF COALESCE(@anyDateTime, '') = '19000101' PRINT 'Equal'; ELSE PRINT 'Different';
IF COALESCE(@anyDateTime, 'Jan 1, 1900') = '1-1-1900' PRINT 'Equal'; ELSE PRINT 'Different';
(If you run with a different language setting, some of the implicit string-to-date conversioins might fail)
June 17, 2015 at 1:58 am
Thanks Hugo,
Now it's clear to me.
June 17, 2015 at 4:25 am
Excellent question. It was unambiguous and the explanation was good. Thanks.
June 17, 2015 at 4:57 am
This was removed by the editor as SPAM
June 17, 2015 at 5:35 am
Nice question.
Disturbing to see that 49.7% still either don't know that coalesce picks the type of its highest type precedence argument or don't know that RTRIM requires conversion of any non-string first argument to a string type.
Tom
June 17, 2015 at 10:00 am
now i'm just wondering how a column could be both Date and DateTime 🙂
"...a column of Date and/or DateTime..."
June 17, 2015 at 11:07 am
Got it right by accident. Long day and logic was off for result from RTRIM - for some reason I was thinking it would return nothing not '' (empty string), which was wrong for two reasons. 🙂
June 17, 2015 at 11:08 am
Interesting question. The question is not about COALESCE, rather it is about RTRIM. COALESCE would have returned a DateTime that WOULD have been equal to the blank string (hence my answer). I made a slight assumption that the question (based on the title) was about the COALESCE not the RTRIM and so mentally omitted the RTRIM, which would yield the first answer. This question is about RTRIM behavior with a non-string data type. Don't really care about points, but the title is very misleading.
June 17, 2015 at 11:49 am
Not quite, it's still the COALESCE. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is "true" because of the zero date (from the COALESCE) coerced into an empty string, but when the COALESCE is further used in an expression it continues as a DATE type. As an example "select coalesce(cast (null as date),'')" returns a date rather than the often assumed empty string.
June 17, 2015 at 12:49 pm
pparsons (6/17/2015)
Not quite, it's still the COALESCE. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is "true" because of the zero date (from the COALESCE) coerced into an empty string, but when the COALESCE is further used in an expression it continues as a DATE type. As an example "select coalesce(cast (null as date),'')" returns a date rather than the often assumed empty string.
No, this is not entirely correct. In a comparison such as "select case when coalesce(cast (null as date),'')='' then 'true' else 'false' end" the result is indeed "true". However, that is NOT because of zero date (from the COALESCE) is coerced into an empty string, but because the comparison between a zero date and an empty string prompts an implicit conversion of the empty string into date.
June 17, 2015 at 2:01 pm
Thanks to Phil for an interesting question and to everyone for interesting discussion!
June 18, 2015 at 12:19 am
good question. thanks for sharing
June 18, 2015 at 2:53 am
Excellent question. Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply