raise the flag if the date exceeds 1 day or 24 hours

datediff() is not the good tool for this task. It increments by 1 every time a boundary is crossed, which produces somehow counter-intuitive results. Typically, the hour difference between today at 13:59 and today at 14:01 is 1.

You can use simple date arithmetics, as follows:

select s.*,
    case when enddte > dateadd(day, 1, begindte) then 1 else 0 end as flag
from shipment s
where ship_id = 14723

Of course, this assumes that the two columns are of a legitimate date-like datatype, such as datetime or datetime2.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top