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:

   <source lang="sql">

SQL> SELECT ADD_MONTHS("01-JAN-2005", 13)

 2  FROM dual;

ADD_MONTH

---

01-FEB-06 SQL></source>

Add Negative time

   <source lang="sql">

SQL> SQL> SQL> SELECT TO_CHAR(TO_CHAR(ADD_MONTHS(SYSDATE, -23), "ddMONyyyy")) "- 23 mon"

 2  FROM dual;

- 23 mon

---

29JUN2005 SQL> SQL></source>

Combine ADD_MONTHS() function with To_Date

   <source lang="sql">

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></source>

Combine ADD_MONTHS() with TO_CHAR() and TO_DATE

   <source lang="sql">

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></source>

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

   <source lang="sql">

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

---

01-APR-05 SQL> SQL></source>

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

   <source lang="sql">

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

---

01-OCT-04</source>

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

   <source lang="sql">

ADD_MONTH

---

28-DEC-05</source>

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

   <source lang="sql">

ADD_MONTH

---

29-DEC-05</source>

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

   <source lang="sql">

ADD_MONTH

---

31-DEC-05</source>

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

   <source lang="sql">

ADD_MONTH

---

30-NOV-05 SQL></source>

Subtract 13 months from the January 1, 2005

   <source lang="sql">

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

---

01-DEC-03 SQL></source>