SQL Server: Datediff Function Resulted In An Overflow When Using MILLISECOND

I have the following query : select CONVERT(varchar(12), DATEADD(MILLISECOND, DateDiff(MILLISECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114) When I execute t

Solution 1:

A bit later response but may help. In SQL 2016 MS introduced function DATEDIFF_BIG which will (according to type size) overflow in difference bigger than something like 290k years. But technet article have same time difference as basic DATEDIFF - https://msdn.microsoft.com/en-us/library/mt628058.aspx

Solution 2:

See https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15#return-value

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds.

If you need millisecond above that level, you'll need to write something custom.

Solution 3:

In SQL Server 2016 there is a new function available: DATEDIFF_BIG

It solves exactly the overflow problem.

Solution 4:

You don't need to refer to the miliseconds in your calculation.

This will do exactly the same as your script except the overflow:

SELECT CONVERT(varchar(12), 
        CAST('2014-11-04 08:21:17.723' as datetime) - 
        CAST('2014-08-04 10:37:28.713' as datetime)
       , 114)

Solution 5:

For me there was a big interval between two dates so i have used below code

declare @timetagInMillsecond bigint=CAST(CAST( cast(@timetag as datetime) -'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)

It works for me .