shole shole - 2 months ago 8
SQL Question

T-SQL: select data which datediff between dates exceed maximum unit

I have a table with two

DateTime
columns,
start
&
end


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
start
and
end
is around 68 years.

Currently there are some false legacy data, which the difference between
start
and
end
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

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

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

or:

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

EDIT:

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

Comments