ADD_MONTHS() function adds a number of month (n) to a date and returns the same day n of month away.
The following illustrates the syntax of the
ADD_MONTHS() function accepts two arguments:
date_expression argument is a DATE value or any expression that evaluates to a DATE value to which the number of month is added.
month argument is an integer that represents a number of months which adds to the first argument.
month argument can be zero, positive or negative. A positive month value allows you to go forward in month while a negative month value bring you backward in month.
ADD_MONTHS() returns a
DATE value with the number of months away from a date.
date_expression is the last day of the month, the resulting date is always the last day of the month e.g., adding 1 month to 29-FEB-2016 will result in 31-MAR-2016, not 29-MAR-2016.
In case the resulting date whose month has fewer days than the day component of
date_expression, the resulting date is the last day of the month. For example, adding 1 month to 31-JAN-2016 will result in 29-FEB-2016.
Otherwise, the function returns a date whose day is the same as the day component of the
A) Add a number of months to a date
The following example adds 1 month to 29-FEB-2016:
SELECT ADD_MONTHS( DATE '2016-02-29', 1 ) FROM dual;
The result is:
B) Add a negative number of months to a date
The following statement illustrates the effect of using a negative month for the
SELECT ADD_MONTHS( DATE '2016-03-31', -1 ) FROM dual;
Here is the result:
As you can see, the
ADD_MONTHS() function brings the date backward in this case.
C) Get the last day of the last month
The following statement returns the last day of the last month.
SELECT LAST_DAY( ADD_MONTHS(SYSDATE , - 1 ) ) FROM dual;
In this example, first, we used the
SYSDATE function to get the current system date and then called the
ADD_MONTHS() function to return the same day of the last month. Finally, we applied the
LAST_DAY() function to return the last day of the month of the resulting date.
In this tutorial, you have learned how to use the Oracle
ADD_MONTHS() function to add a number of month to a date.