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.