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.

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.

3 thoughts on “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.

  1. IMHO – err is at the assumption is that the last day of the month is returned.
    Consider “select dateadd(Month,1,’2012-11-30′) ” – you would not expect ‘2012-12-31’ would you?

    Month-end to month-end could be:
    Declare @ExDate Date = ‘2012-01-31’
    Declare @CalcDate Date = @ExDate
    Declare @Date table (Date date primary key)
    while(@CalcDate < '2013-01-01')
    BEGIN
    insert into @Date Select DateAdd(d,- datepart(d,DateAdd(m,2,@CalcDate)),DateAdd(m,2,@CalcDate))
    set @CalcDate = DateAdd(d,- datepart(d,DateAdd(m,2,@CalcDate)),DateAdd(m,2,@CalcDate))
    End
    Select * from @Date order by date

    Booksonline:
    If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:
    SELECT DATEADD(month, 1, ‘2006-08-30’);
    SELECT DATEADD(month, 1, ‘2006-08-31’);

Leave a Reply

Your email address will not be published. Required fields are marked *