Monday, November 3, 2008

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

5 comments:

László said...

We've run into this problem recently, so thanks for your nice solution! :)

Tim Myer said...

Jó napot László,
Thanks for the comment, and I'm happy to have been of help. If you come across any improvements, please let me know!
----Tim-----

Lighthouse said...

ONE MAY USE A CASE STRUCTURE IN A QUERY FOR THE SAME EFFECT LIKE THIS
IF A FUNCTION IS NOT POSSIBLE

select case when to_char(curdate, 'DD') < to_char(add_months(curdate, 1), 'DD') THEN curdate + numtoyminterval(1, 'month') ELSE add_months(curdate, 1) end into newdate FROM DUAL;

THANKS

Tim Myer said...

Great addition to this entry! Thanks very much for the case statement.
---Tim---

Raul Alexander said...

Tks a lot!!!