Alex Rivera | Logout

ADD_MONTHS function does not return the correct date in Oracle

Asked 2011-03-18T07:32:16.290
9

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.

sql oracle-database date-arithmetic
Edit
Report

1 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
answered 2011-04-01T07:16:51.413

Your Answer