DateTime in Case

• XPSCodes-667952

  Mr or Mrs. 500

  Points: 583

  More actions

  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]

• pdross2000

  Old Hand

  Points: 363

  More actions

  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

• XPSCodes-667952

  Mr or Mrs. 500

  Points: 583

  More actions

  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]