Datediff with NULL date

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  February 20, 2016 at 11:05 pm

  #100070

  HOw to get Datediff (dd, date1, date2) ? date1 comes from a table and date2 drrived in he query with a case statement. date2 is sometimes NULL.

  I derrive date2 as : case when z then xdate

  when y then ydate

  else '' -- this is defaulting to is case condition is not met - > 1900-01-01 00:00:00.000

  So, my datediff values are not coming right. I can't use getdate in coalesce since I need exact datediff between date1 and date2. Is there any get around for this ?

  Thanks,

  VG

• Jeff Moden

  SSC Guru

  Points: 1004403

  More actions

  February 20, 2016 at 11:14 pm

  #1859977

  VG-619426 (2/20/2016)

  ---

  HOw to get Datediff (dd, date1, date2) ? date1 comes from a table and date2 drrived in he query with a case statement. date2 is sometimes NULL.

  I derrive date2 as : case when z then xdate

  when y then ydate

  else '' -- this is defaulting to is case condition is not met - > 1900-01-01 00:00:00.000

  So, my datediff values are not coming right. I can't use getdate in coalesce since I need exact datediff between date1 and date2. Is there any get around for this ?

  Thanks,

  VG

  If you have no Date2, you're not going to get any datediff. You're going to get a NULL. The question is, if DATE2 is NULL, what do you want the answer from the datediff to be?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)