"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())

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.

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.