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

sql-server sql-server-2012 date-difference
Bart Schelkens picture Bart Schelkens · Nov 4, 2014 · Viewed 50.5k times · Source

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

jarabizna picture jarabizna · Apr 14, 2016

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

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Auto increment primary key in SQL Server Management Studio 2012

How do I auto increment the primary key in a SQL Server database table, I've had a look through the forum but can't see how. I've looked the the properties but can't see an option, I have seen an answer …

Read More
Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More