<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by John Arnott
»
More Fun With NULL
18 posts, Page 1 of 2
1
2
»»
More Fun With NULL
Rate Topic
Display Mode
Topic Options
Author
Message
john.arnott
john.arnott
Posted Saturday, November 07, 2009 12:14 PM
UDP Broadcaster
Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491,
Visits: 3,010
Comments posted to this topic are about the item
More Fun With NULL
Post #815462
kaspencer
kaspencer
Posted Sunday, November 08, 2009 5:01 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 1,418,
Visits: 267
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
Dietmar Weickert
Dietmar Weickert
Posted Monday, November 09, 2009 1:49 AM
SSCommitted
Group: General Forum Members
Last Login: Friday, January 11, 2013 8:20 AM
Points: 1,608,
Visits: 373
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
kaspencer
kaspencer
Posted Monday, November 09, 2009 2:11 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 1,418,
Visits: 267
Yes, my mistake Dietmar! I think I was half-asleep!
Ken.
Post #815671
cengland0
cengland0
Posted Monday, November 09, 2009 2:39 AM
Ten 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
BudaCli
BudaCli
Posted Monday, November 09, 2009 4:44 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 1,046,
Visits: 575
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
majorbloodnock
majorbloodnock
Posted Monday, November 09, 2009 6:05 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 10:52 AM
Points: 1,043,
Visits: 2,945
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
Andy Lennon
Andy Lennon
Posted Monday, November 09, 2009 7:20 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386,
Visits: 823
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
sknox
sknox
Posted Monday, November 09, 2009 8:01 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 1,059,
Visits: 1,396
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
DavidSimpson
DavidSimpson
Posted Monday, November 09, 2009 8:49 AM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 892,
Visits: 936
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 »
18 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
