Tag Archives: DATEADD

Sql Server’s DATEADD function may return different result in a scenario where we are adding one month at a time from adding multiple months at a time to the date.

Sql Server,

Today, I came across a scenario were observed that DATEADD function is returning different result if we add 1 month at time from adding multiple months at a time. At the first instance results were surprising to me, later found it to be logically correct. May be for most of you these  results were obvious, but at first look it surprised me 🙂

Let us understand this with an example:

Declare @ExDate DATE = '2012-01-31'
SELECT @ExDate AS ExDate,
DATEADD(MONTH,2,@ExDate) AS AddTwoMonths,
DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)) AddOneMonthThenOneMoreMonth

RESULT:

ExDate     AddTwoMonths AddOneMonthThenOneMoreMonth
---------- ------------ ---------------------------
2012-01-31 2012-03-31   2012-03-29
(1 row(s) affected)

In the above example I was expecting DATEADD(MONTH,2,@ExDate) same as DATEADD(MONTH,1,DATEADD(MONTH,1,@ExDate)), as at the end of the day we are adding two months in both the cases. Finally, after seeing results of this example we can conclude that adding one month shouldn’t go from Jan to March or shouldn’t bring a non existing date in  Feb i.e. 31st Feb.

This behaviour is not specific to Sql Server, same behaviour can be observed in C# too.

Please correct me, if my understanding is wrong. Comments are always welcome.

Note: All the examples in this article are tested on Sql Server 2008 version. And in this article using the new Date data type introduced in Sql Server 2008.