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.