Handling NULL

  • kapil_kk

    SSC-Insane

    Points: 21316

    Comments posted to this topic are about the item Handling NULL

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good question kapil.

    I feel that this question contains it's answer in itself. Why i think so? Let me tell you.

    In question, you told that you need the output like:- "Apr 9 2013 11:38AM".

    This output comes when we convert any datetime in varchar (string format) with different format option. like:-

    SELECT CONVERT(VARCHAR, GETDATE(),100), CONVERT(VARCHAR, GETDATE(),101), CONVERT(VARCHAR, GETDATE(),110)

    This one point to correct answer. πŸ™‚

    One minor mistake i found that, You need output like "Apr 9 2013 11:38AM", But below all queries give today day. oops !!! πŸ™‚

    No problem.

    Thanks of start my day with good question.

    πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • kapil_kk

    SSC-Insane

    Points: 21316

    One minor mistake i found that, You need output like "Apr 9 2013 11:38AM", But below all queries give today day. oops !!! πŸ™‚

    Hi Vinay,

    Actually, I run this scenario on 9 April and put it here for QOTD. I was not aware when it will going to be published....

    Hmmm I missed the word 'like' πŸ˜› it should be written as 'If I want output like this:' :hehe:

    Thanks for prompting me πŸ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • This was removed by the editor as SPAM

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Nice one, thank you for posting.

    (By seeing the output table.. it was sure that date is today's and from the options... I selected 4 quickly and then... realized the format of the date in the output is not same as getdate()'s, so I confirmed "okay it needs convert..." and option was 3... okay... finally I got the answer right; but "damn!! it showed the answer as wrong and was wondering... and again realized.. I did not switch the answer to 3... " Note to self: mental analysis is good, but online submission needs accurate practical selection.)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Be careful with omitting information in your SQL.

    For example, it is good practice to explicitly specify the nullability of your columns.

    Had ANSI_NULL_DFLT_OFF for the session, the insert would fail.

    Msg 515, Level 16, State 2, Line 9

    Cannot insert the value NULL into column 'IsAvailable', table 'tempdb.dbo.#Test_______________________________________________________________________________________________________________000000000044'; column does not allow nulls. INSERT fails.

    SET ANSI_NULL_DFLT_OFF (Transact-SQL)

    Also, in the CONVERTS, you should specify the size to avoid issues and misunderstandings.

    convert(varchar(30), colxyz)

    instead of

    convert(varchar, colxyz)

    Best Regards,

    Chris BΓΌttner

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Christian Buettner-167247 (4/22/2013)


    Be careful with omitting information in your SQL.

    For example, it is good practice to explicitly specify the nullability of your columns.

    Had ANSI_NULL_DFLT_OFF for the session, the insert would fail.

    Msg 515, Level 16, State 2, Line 9

    Cannot insert the value NULL into column 'IsAvailable', table 'tempdb.dbo.#Test_______________________________________________________________________________________________________________000000000044'; column does not allow nulls. INSERT fails.

    SET ANSI_NULL_DFLT_OFF (Transact-SQL)

    Also, in the CONVERTS, you should specify the size to avoid issues and misunderstandings.

    convert(varchar(30), colxyz)

    instead of

    convert(varchar, colxyz)

    Agreed;

    But if you use session property and check the default value for ANSI_NULLS is always 1. (I guess in production servers this may be the case for some specific SPs or any SQL batch where such explicit usage is needed, but in general it is not necessary, we can just consider the SQL defaults and work with that.)

    and data size mention is good but on the GetDate value it does not effects even if you mention 100 (apart from that, regular varchar/char columns, it is good practice)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Thomas Abraham

    SSChampion

    Points: 10761

    Thank you for you contribution. More please.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Kapil good going...

    Good chance to recall return types & Data type precedence of ISNULL,CONVERT & CASE...

    --
    Dineshbabu
    Desire to learn new things..

  • sujay 24116

    Grasshopper

    Points: 14

    If I am not wrong... This may be easier to understand.

    ----------------------------------------------------------------------------------------

    SELECT ID, ISNULL(CAST(IsAvailable AS VARCHAR), GETDATE()) AS IsAvailable FROM #Test

    -----------------------------------------------------------------------------------------

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Dana Medley

    SSCertifiable

    Points: 6764

    Thanks for the great question.



    Everything is awesome!

  • Primo Dang

    SSCrazy

    Points: 2643

    You can eliminate options 1 and 2 because they don't name the second result column as IsAvailable, as it is shown in the desired output.

    Of course, I wasn't sure if this detail was intentional or not, so I checked all of the queries anyway. πŸ˜›

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    friends, since i am new to sql, i have little confusion with this query ( i.e. choice number two and four)

    Choice two: SELECT ID, COALESCE(IsAvailable, GETDATE()) FROM #Test

    choice four: SELECT ID, CASE WHEN IsAvailable IS NULL THEN GETDATE() ELSE IsAvailable END IsAvailable FROM #Test

    # now, wat i know coalesce is that it check for first not null, and then why does Isavailable column value 1 gets converted into '1900: .........' it should have been just 1 (i.e. as it is in table )

  • Bangla

    Hall of Fame

    Points: 3137

    Nice Question....

Viewing 15 posts - 1 through 15 (of 19 total)

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