shole shole - 1 year ago 62
SQL Question

TSQL: select data which datediff between dates exceed maximum unit

I have a table with two DateTime columns,


I have a stored procedure which has a line like

select ...
where datediff(second, start, end) > xxx

I know for unit = second, the maximum difference between
is around 68 years.

Currently there are some false legacy data, which the difference between
is over 68 years, and when it came across this stored procedure, it will produce overflow error.

What I am trying to do is to write another script to select all such false data so that we can patch them, how can I do that? How can I select some records to fix the error which producing the error itself?

Answer Source

First, is it really necessary to do this to one second accuracy. After all:

where datediff(minute, start, end) > xxx / 60


where datediff(hour, start, end) > xxx / (60 * 60)

but . . . if that won't do, you can try:

where dateadd(hour, xxx / (60 * 60),
              dateadd(second, xxx % (60 * 60), start)
             ) > end


Actually, your problem is with the dates, not the xxx value. So, this should also work:

where dateadd(second, xxx, start) > end

This will work as long as xxx is an integer and start is not way too big (near the end of the range of whatever type it is).