More Fun With NULL

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

  • 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

  • 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.

  • 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

  • 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.

  • 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
  • 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

  • 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.

  • 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.

  • 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

  • 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!

  • 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!

  • 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.

  • 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

  • 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