Custom code similar to DATEDIFF_BIG in SQL Server 2014

Question

I am using SQL Server 2014. I am facing a problem when I want to compare previous row date time with current row in second. The error states:

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.

SQL Server 2016 solved this issue with DATEDIFF_BIG but that function is not supported in SQL Server 2014. And currently there are no plans to change database server with SQL Server 2016.

Any alternative solution would be appreciated.

SELECT ROW_NUMBER() OVER (Order by A.MDetailID) as Row
  , DATEDIFF(SECOND, A.CreatedDate, LEAD(A.CreatedDate,1,0) OVER (ORDER BY A.CreatedDate))
FROM dbo.tbl_VehicleLiveMovementDetail A

Messy

Can you share an example of your data with the issue? – Piscatelli

@Jaime It's an example: SELECT ROW_NUMBER() OVER (Order by A.MDetailID) as Row, DATEDIFF(SECOND, A.CreatedDate, LEAD(A.CreatedDate,1,0) OVER (ORDER BY A.CreatedDate)) FROM dbo.tbl_VehicleLiveMovementDetail A – Messy

If the difference in seconds causes an overflow, maybe you need to use minutes instead to get smaller numbers – Gar

@ marc_s but i need to compare with second. – Messy

Answer 1

According to your code,

LEAD(A.CreatedDate,1,0) OVER (ORDER BY A.CreatedDate)

LEAD function provides access to a row at a given physical offset that follows the current row but at the last row LEAD function not found any subsequent row so it return default datetime '1900-01-01 00:00:00' . For this case you need to compare with default datetime like as following....

case when LEAD(A.CreatedDate,1,0) OVER (ORDER BY A.CreatedDate)='1900-01-01 00:00:00' then A.CreatedDate else LEAD(A.CreatedDate,1,0) OVER (ORDER BY A.CreatedDate) end

which would not be optimum solution for you.

If you use LEAD(A.CreatedDate,1,A.CreatedDate) instead of LEAD(A.CreatedDate,1,0), you would not be required to use any case statement and the solution would be optimum like as following...

SELECT 
ROW_NUMBER() OVER (Order by A.MDetailID) as Row,
DATEDIFF(SECOND, A.CreatedDate, LEAD (A.CreatedDate,1,A.CreatedDate)OVER (ORDER BY A.CreatedDate))Result
FROM dbo.tbl_VehicleLiveMovementDetail A

Endometriosis

instead of LEAD(A.CreatedDate,1,0) use convert ( datetime, LEAD (A.CreatedDate,1,A.CreatedDate).. And the remove case when ... then .. else ... end – Xerosere