November 7, 2009 at 12:14 pm
Comments posted to this topic are about the item More Fun With NULL
November 8, 2009 at 5:01 am
Interesting answer.
I tried this without looking anything up, and got some of it wrong.
Then I ran the queries ... and in my case, the queries that were stated as failures in your answer did indeed fail.
BUT although several answers did not return errors, NONE actually returned the quoted string: they all seemed to return NULL.
Did anyone else get similar results?
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
November 9, 2009 at 1:49 am
Hi,
I tried it as well and got exactly the predicted results. You must, however, be aware that result sets ("Date is Null"; "Date is Nu") and the messages ("Option 1:", errors, etc.) are shown in separate windows.
Best regards,
Dietmar Weickert.
November 9, 2009 at 2:11 am
Yes, my mistake Dietmar! I think I was half-asleep!
Ken.
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer
November 9, 2009 at 2:39 am
I almost selected option 6 and then when I double checked my answers, I noticed the tricky char(10) which isn't big enough to fit the whole 'Date is Null' string. It needed to be char(12) to give the right results.
Very sneaky question but I did get this one.
November 9, 2009 at 4:44 am
Nice one, Noticed that when you use your case statement as per below then you get the "Date is Null" results as you do not have your else statement.
SELEC CASE
WHEN CONVERT(CHAR(10),@MyDate,101) IS NULL THEN 'Date is Null'
END AS MyDate
November 9, 2009 at 6:05 am
Which of these seven queries will return "Date is Null"? (select all that apply)
Was that a zero-based list? ;-):-D
Semper in excretia, suus solum profundum variat
November 9, 2009 at 7:20 am
cengland0 (11/9/2009)
I almost selected option 6 and then when I double checked my answers, I noticed the tricky char(10) which isn't big enough to fit the whole 'Date is Null' string. It needed to be char(12) to give the right results.Very sneaky question but I did get this one.
yep, i missed that. phooey.
November 9, 2009 at 8:01 am
duda (11/9/2009)
Nice one, Noticed that when you use your case statement as per below then you get the "Date is Null" results as you do not have your else statement.SELEC[T] CASE
WHEN CONVERT(CHAR(10),@MyDate,101) IS NULL THEN 'Date is Null'
END AS MyDate
True, but you don't get the date when @MyDate is not null either.
All of the queries are examples of trying to return @MyDate when it's not null, and "Date is Null" otherwise.
November 9, 2009 at 8:49 am
Good question. I missed the char(10) limitation. I did not know that COALESCE does not limit output size while ISNULL does.
coalesce(convert(char(10),@MyDate,101),'Date is Null') = not limited to 10 characters
IsNull(convert(char(10),@MyDate,101),'Date is Null') = limited to 10 characters
Now just have to try and remember this :hehe:
David
November 9, 2009 at 9:02 am
Option 6 fooled me too - but I'm always glad to learn more about the differnces between ISNULL and COALESCE, since they often appear to be interchangeable!
November 9, 2009 at 9:29 am
majorbloodnock (11/9/2009)
Which of these seven queries will return "Date is Null"? (select all that apply)
Was that a zero-based list? ;-):-D
Noticed that, but since the PRINT statements utilize 1 through 8, the answer is clear :-).
Nice question, John!
November 9, 2009 at 10:43 am
Interesting question.
I got it right just because I executed it in SSMS and saw results. But without executing I would had selected option 6 also.
SQL DBA.
November 9, 2009 at 11:02 am
Doh, option 6 tripped me up - should have read it more closely.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 9, 2009 at 11:48 am
Michael Poppers (11/9/2009)
majorbloodnock (11/9/2009)
Which of these seven queries will return "Date is Null"? (select all that apply)
Was that a zero-based list? ;-):-D
Noticed that, but since the PRINT statements utilize 1 through 8, the answer is clear :-).
Nice question, John!
Major:
After doing a Homer Simpson impression ("Doh!"), I had to laugh. Even after re-reading several times, I missed that (seven vs eight). I guess that's why we are taught to have others review our work.
Michael (and others),
Thank you for the positive feedback.
I probably should have mentioned in the "Explanation" that the easiest way to see the full picture is to run the script after switching to "Results to Text".
------
edit: minor typo: "picture", not "pecture".
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply