Query:
; with t as
( SELECT [ID],
[LastRunTime],
LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime]) AS LAGLastRunTime
, .....
FROM .....
)
SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_
FROM t
Error message:
Msg 535, Level 16, State 0, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Reason for the error:
The “guilty” is the first record – that don’t have LAG date – so the LAG value is ‘1900-01-01 00:00:00.000’.
And then –datediff can’t go with so big int value - the seconds/minutes/etc. between 1900 to the first date – that is probably more than 100 years… J
Solution:
Convert the first date to some reasonable date:
; with t as
( SELECT [ID],
[LastRunTime],
( case
when LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime]) > '20010101'
then LAG([LastRunTime], 1,0) OVER (ORDER BY [LastRunTime])
else [LastRunTime] end
) AS LAGLastRunTime
, .....
FROM .....
)
SELECT *, DATEDIFF(second, LAGLastRunTime , isnull(LastRunTime, '20010101')) as delta_
FROM t