DATEADD issue when negative number is used with MONTH datepart

Question

From the DATEADD documentation:

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');

The SQL Server knows that the last day of 2016-03 is 31 and the last day of the 2016-04 is 30:

SELECT DAY(EOMONTH('2016-03-01')) -- 31
SELECT DAY(EOMONTH('2016-04-01')) -- 30

Then why the following:

SELECT DATEADD(MONTH, -1, '2016-04-30')

returns 2016-03-30 00:00:00.000 instead 2016-03-31 00:00:00.000?

Also, if I have the following:

SELECT  DATEADD(MONTH, -1, '2016-03-31')

it correctly returns 2016-02-29 00:00:00.000.

..."and the date day does not exist in the return month"... - since the 30th day does exist in march, it is correctly returning the 30th of march — pastacool
if the EOMONTH function isn't enough for you and you want' to stick to some DATEADD kind of approach, you could use something like SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()), 0) - 1 LastDayOfMonth — pastacool
If you always need the last day of the month, always use the 31st of a month as your starting point. It's as simple as that. (Or, compute the 1st day of the following month instead and use < rather than <=) — Damien_The_Unbeliever

George Dando George Dando · Accepted Answer · 2016-04-14T12:59:49

As other comments have said, if the day of the month exists in the previous month, DATEADD will use it, and not assume that you want "the last day of the month".

If you do indeed want the "Last day of the month", you'll have to knock up a bit more logic such as:

DECLARE @date DATETIME = '30 April 2016'
SELECT CASE WHEN DATEDIFF(MONTH, @date, DATEADD(DAY, 1, @date)) = 1 THEN     DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0))
        ELSE DATEADD(MONTH, -1, @date)
   END

That will assume that if it's the last day of the month, you want the last day of the month in the previous month, and not explicitly the 28th, 29th or 30th.

DATEADD issue when negative number is used with MONTH datepart

3 Answers