SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Fun With NULL


More Fun With NULL

Author
Message
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2004 Visits: 3059
Comments posted to this topic are about the item More Fun With NULL
kaspencer
kaspencer
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2480 Visits: 837
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
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 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.
kaspencer
kaspencer
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2480 Visits: 837
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
cengland0
cengland0
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1690 Visits: 1300
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.
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1268 Visits: 598
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
majorbloodnock
majorbloodnock
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1495 Visits: 3062
Which of these seven queries will return "Date is Null"? (select all that apply)


Was that a zero-based list? ;-):-D

Semper in excretia, sumus solum profundum variat
Andy Lennon
Andy Lennon
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 826
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.
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2632 Visits: 2832
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.
DavidSimpson
DavidSimpson
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1152 Visits: 1077
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search