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.
We have 29th because 2012 is a leap year?
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’);
Interesting,
and 28.02.2019 one month back, gives us 28.01.2019 (at least in VBA ADO SQL)