Post reply

DateTime in Case

  • April 6, 2008 at 10:08 am

    #188707

    I'm curious why the first query doesn't work?

    Create Table DateTimeCheck

    (ID int Identity(1,1),

    Date datetime Null)

    Insert DateTimeCheck

    Select Null Union All

    Select getdate() Union All

    Select getdate() Union All

    Select Null

    Select ID,Case Date When Null Then GetDate() Else Date End From DateTimeCheck

    Select ID,IsNull(Date,GetDate()) From DateTimeCheck

    OutPut

    ID

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

    1 NULL

    2 2008-04-06 12:07:23.920

    3 2008-04-06 12:07:23.920

    4 NULL

    (4 row(s) affected)

    ID

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

    1 2008-04-06 12:07:27.590

    2 2008-04-06 12:07:23.920

    3 2008-04-06 12:07:23.920

    4 2008-04-06 12:07:27.590

    --------------------------------------------------------------------------------------
    Save our world, its all we have! A must watch video Pale Blue Dot[/url]

  • April 6, 2008 at 10:23 am

    #798871

    CASE is using the = comparison not the IS comparison needed to handle NULLS

    the correct case would be

    Select ID,Case When Date is Null Then GetDate() Else Date End From DateTimeCheck

  • April 6, 2008 at 10:33 am

    #798873

    Yea, Right my bad :hehe: Thanks

    --------------------------------------------------------------------------------------
    Save our world, its all we have! A must watch video Pale Blue Dot[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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