The Oracle/PLSQL ADD_MONTHS function returns a date plus n months.
ADD_MONTHS('01-Aug-03', 3)
---------------------------- ww w . j a va 2 s. c o m
'01-Nov-03'
ADD_MONTHS('01-Aug-03', -3)
--------------------------
'01-May-03'
ADD_MONTHS('21-Aug-03', -3)
--------------------------
'21-May-03'
ADD_MONTHS('31-Jan-03', 1)
--------------------------
'28-Feb-03'
This Oracle tutorial explains how to use the Oracle/PLSQL ADD_MONTHS function.
ADD_MONTHS(x, y) returns the result of adding y months to x.
If y is negative, then y months are subtracted from x.
The syntax for the Oracle/PLSQL ADD_MONTHS function is:
ADD_MONTHS( date1, n )
date1 is the starting date.
n is the number of months to add to date1.
Adding 13 months:
SQL> SELECT ADD_MONTHS('01-JAN-2012', 13) FROM dual;
ADD_MONTH
---------
01-FEB-13
SQL>
Subtract 13 months:
SQL> SELECT ADD_MONTHS('01-JAN-2012', -13) FROM dual;
ADD_MONTH
---------
01-DEC-10
SQL>
Add two months to the date and time:
SQL> SELECT ADD_MONTHS(TO_DATE('01-JAN-2012 19:15:26','DD-MON-YYYY HH24:MI:SS'),
2) FROM dual;
ADD_MONTH
---------
01-MAR-12
SQL>
SQL>
Combine ADD_MONTHS() and TO_CHAR() with the format DD-MON-YYYY HH24:MI:SS:
SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01-JAN-2012 19:15:26','DD-MON-YYYY HH24:
MI:SS'), 2), 'DD-MON-YYYY HH24:MI:SS') FROM dual;
TO_CHAR(ADD_MONTHS(T
--------------------
01-MAR-2012 19:15:26
SQL>