ADD_MONTHS function does not return the correct date in Oracle

Question

See the results of below queries:

>> SELECT ADD_MONTHS(TO_DATE('30-MAR-11','DD-MON-RR'),-4) FROM DUAL;
30-NOV-10


>> SELECT ADD_MONTHS(TO_DATE('30-NOV-10','DD-MON-RR'),4) FROM DUAL;
31-MAR-11

How can I get '30-MAR-11' when adding 4 months to some date?

Please help.

Answer 1

There is another question here about Oracle and Java

It states that

From the Oracle reference on add_months http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm

If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

So I guess you have to manually check stating day and ending day to change the behaviour of the function. Or maybe by adding days instead of months. (But I didn't find a add_day function in the ref)

Answer 2

As a workaround, I might possibly use this algorithm:

1. Calculate the target date TargetDate1 using ADD_MONTHS.

2. Alternatively calculate the target date TargetDate2 like this:

   1) apply ADD_MONTHS to the first of the source date's month;
   2) add the difference of days between the source date and the beginning of the same month.

3. Select the LEAST between the TargetDate1 and TargetDate2.

So in the end, the target date will contain a different day component if the source date's day component is greater than the number of day in the target month. In this case the target date will be the last day of the corresponding month.

I'm not really sure about my knowledge of Oracle's SQL syntax, but basically the implementation might look like this:

SELECT
  LEAST(
    ADD_MONTHS(SourceDate, Months),
    ADD_MONTHS(TRUNC(SourceDate, 'MONTH'), Months)
      + (SourceDate - TRUNC(SourceDate, 'MONTH'))
  ) AS TargetDate
FROM (
  SELECT
    TO_DATE('30-NOV-10', 'DD-MON-RR') AS SourceDate,
    4 AS Months
  FROM DUAL
)

---

Here is a detailed illustration of how the method works:

SourceDate = '30-NOV-10'
Months     = 4

TargetDate1 = ADD_MONTHS('30-NOV-10', 4) = '31-MAR-11'  /* unacceptable */
TargetDate2 = ADD_MONTHS('01-NOV-10', 4) + (30 - 1)
            = '01-MAR-11' + 29 = '30-MAR-11'            /* acceptable */
TargetDate  = LEAST('31-MAR-11', '30-MAR-11') = '30-MAR-11'

And here are some more examples to show different cases:

SourceDate | Months | TargetDate1 | TargetDate2 | TargetDate
-----------+--------+-------------+-------------+-----------
 29-NOV-10 |    4   |   29-MAR-11 |   29-MAR-11 |  29-MAR-11
 30-MAR-11 |   -4   |   30-NOV-10 |   30-NOV-10 |  30-NOV-10
 31-MAR-11 |   -4   |   30-NOV-10 |   01-DEC-10 |  30-NOV-10
 30-NOV-10 |    3   |   28-FEB-11 |   02-MAR-11 |  28-FEB-11

Answer 3

You can use interval arithmetic to get the result you want

SQL> select date '2011-03-30' - interval '4' month
  2    from dual;

DATE'2011
---------
30-NOV-10

SQL> ed
Wrote file afiedt.buf

  1  select date '2010-11-30' + interval '4' month
  2*   from dual
SQL> /

DATE'2010
---------
30-MAR-11

Be aware, however, that there are pitfalls to interval arithmetic if you're working with days that don't exist in every month

SQL> ed
Wrote file afiedt.buf

  1  select date '2011-03-31' + interval '1' month
  2*   from dual
SQL> /
select date '2011-03-31' + interval '1' month
                         *
ERROR at line 1:
ORA-01839: date not valid for month specified