The datediff function resulted in an overflow in Microsoft SQL Server
Error: while executing one of diff function in the SQL Procedure getting the following error:
SELECT DATEDIFF(s, '2020-01-01 10:10:10.000', '2322-01-01 11:45:00.000'); 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.
Cause: Datadiff function returns an integer. The biggest number of the integer in SQL is 2,147,483,647. If its return value out of this range then you will get the similar error.
DATEDIFF function has return type INT which Min and Max value is: -2,147,483,648 to +2,147,483,647.
DATEDIFF_BIG function has return type is bigint which Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
- If its related to bad data in data format, then you can correct your data.
- Option : Simple use the datadiff_big function instead of datadiff function
Use of datadiff_big is same as shown below:
SELECT DATEDIFF_BIG(s, '2020-01-01 10:10:10.000', '2322-01-01 11:45:00.000');