Oracle PL/SQL Tutorial/Date Timestamp Functions/ADD MONTHS

ADD_MONTHS(x, y) gets the result of adding y months to x.

If y is negative, y months are subtracted from x.

The following example adds 13 months to January 1, 2005:

SQL> SELECT ADD_MONTHS("01-JAN-2005", 13)
  2  FROM dual;
ADD_MONTH
---------
01-FEB-06
SQL>

Add Negative time

SQL>
SQL>
SQL> SELECT TO_CHAR(TO_CHAR(ADD_MONTHS(SYSDATE, -23), "ddMONyyyy")) "- 23 mon"
  2  FROM dual;
- 23 mon
---------
29JUN2005
SQL>
SQL>

Combine ADD_MONTHS() function with To_Date

SQL>
SQL> SELECT ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26",
  2    "DD-MON-YYYY HH24:MI:SS"), 2)
  3  FROM dual;
ADD_MONTH
---------
01-MAR-05
SQL>

Combine ADD_MONTHS() with TO_CHAR() and TO_DATE

SQL>
SQL>
SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26",
  2    "DD-MON-YYYY HH24:MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS")
  3  FROM dual;
TO_CHAR(ADD_MONTHS(T
--------------------
01-MAR-2005 19:15:26
SQL>

SELECT ADD_MONTHS("01-JAN-2005", 3) FROM dual;

SQL> SELECT ADD_MONTHS("01-JAN-2005", 3) FROM dual;
ADD_MONTH
---------
01-APR-05
SQL>
SQL>

SELECT ADD_MONTHS("01-JAN-2005", -3) FROM dual; (2)

SQL> SELECT ADD_MONTHS("01-JAN-2005", -3) FROM dual;
ADD_MONTH
---------
01-OCT-04

SELECT ADD_MONTHS("28-NOV-05", 1) FROM DUAL;

ADD_MONTH
---------
28-DEC-05

SELECT ADD_MONTHS("29-NOV-05", 1) FROM DUAL;

ADD_MONTH
---------
29-DEC-05

SELECT ADD_MONTHS("30-NOV-05", 1) FROM DUAL;

ADD_MONTH
---------
31-DEC-05

SELECT ADD_MONTHS("31-DEC-05", -1) FROM DUAL;

ADD_MONTH
---------
30-NOV-05
SQL>

Subtract 13 months from the January 1, 2005

SQL>
SQL> SELECT ADD_MONTHS("01-JAN-2005", -13) FROM dual;
ADD_MONTH
---------
01-DEC-03
SQL>