Non-ANSI Oracle ADD_MONTHS Function

Motivation

To use an Oracle function for adding months with the following characteristics:

• When the resulting month has as many or fewer days than the initial month, and when the initial day of the month is greater than the number of days in the resulting month, then the resulting day should fall on the last day of the resulting month (this is how add_months already works).
• When the resulting month has more days than the initial month, and when the initial day is the last day of the initial month, then the resulting day of the resulting month should be the same as the initial day (this is not how add_months works).

The Problem

As I had been using the Oracle add_months function for date calculations, I started noticing an unexpected and unintuitive result when a new date is calculated on the last day of certain months. For example,

SELECT add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;

ADD_MONTH
---------
31-MAR-09

I would have expected the resulting date to be 28-MAR-09.

Of course, in the case where the initial month contains more days than the resulting month, I get the results that I expect.

SELECT add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;

ADD_MONTH
---------
28-FEB-09

This feature appears to be part of the ANSI definition for interval math, but this result does not seem particularly intuitive to me.

Unfortunately, the numtoyminterval function only gives the result we expect when go from a month with fewer days to a month with more days, but when going from a month with more days to fewer, it raises an exception when calculating from the last day of the month (or from any day of the month that is greater than the number of days in the resulting month).

SELECT to_date('2009-02-28','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual;

TO_DATE('
---------
28-MAR-09

SELECT to_date('2009-01-31','YYYY-MM-DD') + numtoyminterval(1, 'month') FROM dual
                                          *
ERROR at line 1:
ORA-01839: date not valid for month specified

The Function

The function itself is fairly straightforward using a combination of both add_months and numtoyminterval. When we are going from a month with more days to fewer days, then add_months yields the expected result. If we are going from a date with fewer days in the months to a date with more days in the month, then using numtoyminterval is safe because there will be no overflow.

CREATE OR REPLACE FUNCTION non_ansi_add_months
(  vDate DATE,
   vMonths INTEGER  ) 
RETURN DATE AS
  newDate DATE;
BEGIN
  newDate := add_months(vDate, vMonths);
  IF to_char(vDate, 'DD') < to_char(newDate, 'DD') THEN
    newDate := vDate + numtoyminterval(vMonths, 'month');
  END IF;
  RETURN newDate;
END non_ansi_add_months;

The Result

This function now yields the results we expect.

SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), 1) FROM dual;

NON_ANSI_
---------
28-MAR-09

SELECT non_ansi_add_months(to_date('2009-01-31','YYYY-MM-DD'), 1) FROM dual;

NON_ANSI_
---------
28-FEB-09

The function even works as expected when adding negative months (calculating month intervals in the past). For example,

SELECT non_ansi_add_months(to_date('2009-02-28','YYYY-MM-DD'), -1) FROM dual;

NON_ANSI_
---------
28-JAN-09

SELECT non_ansi_add_months(to_date('2009-03-30','YYYY-MM-DD'), -1) FROM dual;

NON_ANSI_
---------
28-FEB-09