SQL Server: datediff function resulted in an overflow when using MILLISECOND

Question

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 this, I get the error : "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."

When I change the query to the following it works fine :

select CONVERT(varchar(12), DATEADD(SECOND, DateDiff(SECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114)

The problem is that I really need the MILLISECONDS as well.

Answer 1

See https://learn.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.