Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

More Fun With NULL Expand / Collapse
Author
Message
Posted Saturday, November 7, 2009 12:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
Comments posted to this topic are about the item More Fun With NULL
Post #815462
Posted Sunday, November 8, 2009 5:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:51 AM
Points: 1,743, Visits: 485
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?
Post #815545
Posted Monday, November 9, 2009 1:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, Visits: 374
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.
Post #815665
Posted Monday, November 9, 2009 2:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:51 AM
Points: 1,743, Visits: 485
Yes, my mistake Dietmar! I think I was half-asleep!

Ken.
Post #815671
Posted Monday, November 9, 2009 2:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #815684
Posted Monday, November 9, 2009 4:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
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


What you don't know won't hurt you but what you know will make you plan to know better
Post #815730
Posted Monday, November 9, 2009 6:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 8:35 AM
Points: 1,049, Visits: 3,008
Which of these seven queries will return "Date is Null"? (select all that apply)


Was that a zero-based list?


Semper in excretia, sumus solum profundum variat
Post #815768
Posted Monday, November 9, 2009 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:27 PM
Points: 1,386, Visits: 824
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.
Post #815792
Posted Monday, November 9, 2009 8:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 1,333, Visits: 1,701
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.
Post #815809
Posted Monday, November 9, 2009 8:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:38 AM
Points: 930, Visits: 1,003
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

David



Post #815842
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse