DateAdd Column caused an overflow

Question

After executing the following query I am getting an error

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

I have no idea why this is happening as it worked smoothly for a couple of weeks. I am just trying to ADD Dates here and compare them to the Start date and End date with a between clause.

DATEADD(day, -1 , DATEADD(mm, DATEDIFF(mm,0,posting_date),0)) BETWEEN start_date and end_date

score 9 · Accepted Answer · edited May 23 '17 at 12:07

9

I can duplicate the error with the following:

declare @posting_date datetime
set @posting_date = '1/1/1753'

select DATEADD(day, -1 , DATEADD(mm, DATEDIFF(mm,0,@posting_date),0))

error after running it:

Msg 517, Level 16, State 1, Line 3 Adding a value to a 'datetime' column caused an overflow.

Basically, posting_date in the above case is the minimum date time value allowed by SQL server. If you then try to subtract 1 day from it, then it enters an overflow condition.

My guess is that you have a datapoint which is set to SQL Server's minimum date value.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 02 '12 at 20:29

NotMe

87,343
27
171
245

For me `DATEADD(day, -1 , DATEADD(mm, -1753,0))` appears to work, but `DATEADD(day, -1 , DATEADD(mm, -1754,0))` does not. – Sergey Kalinichenko Feb 02 '12 at 20:32
1

@dasblinkenlight: Well.. the first one should result in the date 11/30/1753, which is a valid SQL server date. The second one should eval out to 10/31/1753. Regardless, neither has anything to do with the OP's question – NotMe Feb 02 '12 at 20:38
2

Oops, I made a typo: it was -1763 and -1764 (with 6, not 5), so his call to `DATEDIFF` returns a negative number less than -1764, meaning that his `posting_date` must be at least 1764 months before 1/1/1900. Good catch. – Sergey Kalinichenko Feb 02 '12 at 20:53
THanks guys, Found the error. One record was having 1753-01-01 00:00:00.000 as the Date which was giving the error in the Data Warehouse. – ace_mccloud Feb 02 '12 at 21:01

score 2 · Answer 2 · answered Feb 02 '12 at 20:28

According to MSDN:

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."

What is the data type of the column and the ranges of start_date and end_date?

DateAdd Column caused an overflow

2 Answers2

Linked