Oracle ADD_MONTHS Function

Oracle ADD_MONTHS() function adds a number of month (n) to a date and returns the same day n of month away.

Introduction to the Oracle ADD_MONTHS function #

The ADD_MONTHS() function allows you to ad a number of month to date.

Here’s the syntax of of the ADD_MONTHS() function:

ADD_MONTHS(date_expression, month)Code language: SQL (Structured Query Language) (sql)

The ADD_MONTHS() function accepts two arguments:

• date_expression is a DATE value or any expression that evaluates to a DATE value to which you want to add the number of months.
• month is an integer that represents a number of months you want to add to the date.

The month argument can be zero, positive, or negative. A positive month value allows you to go forward in a month while a negative month value brings you backward in a month.

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.

If 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.

Adding a number of months to a date #

The following example uses the ADD_MONTHS function to add 1 month to 29-FEB-2016:

SELECT
  ADD_MONTHS( DATE '2016-02-29', 1 )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Try it

Output:

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

Adding a negative number of months to a date #

The following statement shows how to add a negative number to a date using the ADD_MONTHS function:

SELECT
  ADD_MONTHS (DATE '2016-03-31', -1)
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

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

The ADD_MONTHS() function brings the date backward in this case.

Getting the last day of the last month #

The following statement uses the ADD_MONTHS function to return 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, the SYSDATE function to get the current system date.
• Second, call the ADD_MONTHS() function to return the same day of the last month.
• Third, the LAST_DAY() function to return the last day of the month of the resulting date.

Summary #

• Use the Oracle ADD_MONTHS() function to add a number of months to a date.