datediff on LAG expression error - The datediff function resulted in an overflow

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