Post reply

Handling NULL

  • April 23, 2013 at 7:27 am

    #1609042

    Mr. Kapsicum (4/23/2013)

    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 )

    A column in a result set can only have a single data type. When mutliple data types are mixed in an expression, there are rules that determine the data type of the result (look for "data type precedence" in Books Online). In the case ogf both expression above, datetime and int are mixed, and the rules say that the column will be of data type datetime. So that means that the integer value 1 has to be implicitly converted to datetime - and that happens to result in Jan 2, 1900.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • April 23, 2013 at 9:47 am

    #1609145

    Thanks for the question

    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

  • May 7, 2013 at 1:06 am

    #1612871

    Another lovely question. 🙂

  • May 31, 2013 at 1:12 am

    #1619963

    simple but really useful problematic

Viewing 4 posts - 16 through 18 (of 18 total)

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