Post reply

Substract End Dates

  • April 17, 2013 at 9:30 am

    #1607303

    Cadavre (4/17/2013)

    Lynn Pettis (4/17/2013)

    Two things. One, why the cross applies here. I don't think they are really needed. Two, the OP did say to treat NULL using getdate() (even though he said it like this: gatedate).

    Replying to your first point, I find it easier to read.

    Replying to your second point, I missed him say anything about getdate but I did spot this: -

    kiran.rajenimbalkar (4/17/2013)

    if end date column would be null then pls treat as 20790606

    I assumed that he'd want the same for a start date, but getdate does make more sense.

    --EDIT--

    Just for completeness, we'd fix what I posted like this: -

    SELECT gg_key, fg_sta_key, fg_eff_dt_key, fg_end_dt_key, fg_seq_nbr,

    DATEDIFF(d,DATE_fg_eff_dt_key,DATE_fg_end_dt_key) AS diffDays

    FROM abc

    CROSS APPLY (SELECT ISNULL(CAST(CAST(fg_eff_dt_key AS VARCHAR(9)) AS DATE), GETDATE()) AS DATE_fg_eff_dt_key,

    CAST(CAST(ISNULL(fg_end_dt_key,20790606) AS VARCHAR(9)) AS DATE) AS DATE_fg_end_dt_key) ca;

    To your first point, yes I can see that.

    To the second, looks like we both missed something.

    Okay. I was just curious.

Viewing post 16 (of 16 total)

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