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

Answer 1

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.

Answer 2

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?