Datediff with NULL date

  • 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

  • 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)

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

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