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

Question

MSDN clearly states that:

The date argument cannot be incremented to a value outside the range of its data type. In the following operations, the numeric value added to the date value exceeds the range of the date data type. The following error message appears: "Adding a value to the" datetime "column caused an overflow."

And an example:

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

which causes the error:

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

That seems right. But why am I getting 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

The first google result for 'sql datetime range' . January 1, 1753. This is your bottom line.

The commentary on the question added this trifle about the origin of this lower boundary.

Answer 2

If you are using a DateTime transform with a field, use the case statement in the transform to check if the field is larger than 1 or 1,000,000, then you no longer have to do this.