14

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())
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 2
    The minimum `datetime` is 1753 [because that was the year after Britain adopted the Gregorian Calendar](http://stackoverflow.com/questions/3310569/what-is-the-significance-of-1-1-1753-in-sql-server/3310588#3310588) What version of SQL Server are you on? – Martin Smith Feb 22 '12 at 19:33
  • 1
    @Joro - Well `SELECT DATEADD(YY,-300,cast(getdate() as datetime2))` will work for you then. – Martin Smith Feb 22 '12 at 21:08
  • @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. – gotqn Feb 26 '12 at 11:52

3 Answers3

15

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.

Community
  • 1
  • 1
Sam DeHaan
  • 10,246
  • 2
  • 40
  • 48
  • 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... – gotqn Feb 22 '12 at 21:02
  • 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). – Smandoli Apr 08 '18 at 22:57
1

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.

Mel van Wyk
  • 11
  • 1
0

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)
Bhadresh Patel
  • 1,671
  • 17
  • 18