Oracle ADD_MONTHS

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)
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

The result is:

31-MAR-16
Code language: SQL (Structured Query Language) (sql)

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; 
Code language: SQL (Structured Query Language) (sql)

Here is the result:

29-FEB-16
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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.