Oracle SQL: the Essential Reference

ADD_MONTHS

ADD_MONTHS(d,n)

Adds n months to the date d. Negative values of n may be used to subtract months from d. The following rules control whether or not the day of the month is affected:

• If the original date represents the last day of its month, the resulting date will be adjusted so that it is also the last day of the month.

• If keeping the same day of the month results in an invalid date, because the new month has fewer days than the original (e.g., January has 31, but February has 28), the day will be adjusted downward to fit the new month.

If neither of the rules listed here applies, the day of the month will not be changed.

Examples

SQL> SELECT ADD_MONTHS(TO_DATE('1-Jan-2000'), 3) FROM dual;

ADD_MONTHS(
-----------
01-Apr-2000

SQL> SELECT ADD_MONTHS(TO_DATE('31-Jan-2000'), 1) FROM dual;

ADD_MONTHS(
-----------
29-Feb-2000

SQL> SELECT ADD_MONTHS(TO_DATE('29-Feb-2000'), -1) FROM dual;

ADD_MONTHS(
-----------
31-Jan-2000