"Adding a value to a 'datetime' column caused an overflow."

Question

In the MSDN is clearly said that:

The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is added to the date value exceeds the range of the date data type. The following error message is returned: "Adding a value to a 'datetime' column caused overflow."

And the example:

SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');

which causes the error:

"Adding a value to a 'datetime' column caused overflow."

This seem right. But why I get the same error executing this SQL statement:

SELECT DATEDIFF(YY,'1013-12-12',DATEADD(YY,-300,getdate()))

more specific and only:

SELECT DATEADD(YY,-300,getdate())

Evasive

The minimum datetime is 1753 because that was the year after Britain adopted the Gregorian Calendar What version of SQL Server are you on? – Winstead

@Joro - Well SELECT DATEADD(YY,-300,cast(getdate() as datetime2)) will work for you then. – Winstead

@MartinSmith Yes, you are right. I have checked this and 'datetime2' and 'datetimeoffset' format support dates from January 1,0001 to December 31 9999. Are the SQL statements till now means, that the default type of getdate() function is 'datetime' and why if it is said that 'datetime' and 'smalldatetime' are depreciated. – Evasive

Answer 1

First google result for 'sql datetime range'. January 1, 1753. That's your lower bound.

A comment on the question added this trivia about the origin of this lower bound.

Concert

Nice. Thank you for the answer, but it is a kind of sad. For example, if someone ones to store historical information in his database before this year, he will not be able to use some built in functions... – Evasive

For dealing with ante-Gregorian periods, design to store the date as text. It makes for some inconvenience, but we can take it philosophically (as they say) because this need is so unusual (even if important). – Caelian

Answer 2

If you do the DateTime conversion with a field use a case statement in the conversion to check if the field is bigger than 1 OR 1000000 then you should not have this problem anymore.

Tamworth

Answer 3

I am getting same error during compare 2 dates.

I have solved with using datetime2 datatype.

For ex.

select * from TableA where Convert(datetime2,GETUTCDATE()) <= Convert(datetime2,Expirydate)

Borrowing