Oracle ADD_MONTHS() function adds a number of month (n) to a date and returns the same day n of month away.
Syntax
The following illustrates the syntax of the ADD_MONTHS() function:
ADD_MONTHS(date_expression, month)
Arguments
The ADD_MONTHS() function accepts two arguments:
1) date_expression
The date_expression argument is a DATE value or any expression that evaluates to a DATE value to which the number of month is added.
2) month
The month argument is an integer that represents a number of months which adds to the first argument.
The 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.
Return value
The ADD_MONTHS() returns a DATE value with the number of months away from a date.
If 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 date_expression.
Examples
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:
31-MAR-16
B) Add a negative number of months to a date
The following statement illustrates the effect of using a negative month for the ADD_MONTH() function:
SELECT
ADD_MONTHS( DATE '2016-03-31', -1 )
FROM
dual;
Here is the result:
29-FEB-16
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.