More Fun With NULL

  • john.arnott

    SSChampion

    Points: 11882

    Comments posted to this topic are about the item More Fun With NULL

  • kaspencer

    SSCarpal Tunnel

    Points: 4274

    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

    SSCrazy

    Points: 2258

    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

    SSCarpal Tunnel

    Points: 4274

    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

    SSCertifiable

    Points: 6102

    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

    Hall of Fame

    Points: 3378

    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

    SSCrazy Eights

    Points: 9267

    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

    SSCrazy

    Points: 2354

    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

    SSChampion

    Points: 12243

    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

    Hall of Fame

    Points: 3542

    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

  • jts2013

    Hall of Fame

    Points: 3226

    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!

  • Michael Poppers

    SSCrazy

    Points: 2120

    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!

  • SanjayAttray

    SSChampion

    Points: 13157

    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.

  • SQLRNNR

    SSC Guru

    Points: 281205

    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

  • john.arnott

    SSChampion

    Points: 11882

    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 19 total)

You must be logged in to reply to this topic. Login to reply