Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions / Oracle ADD_MONTHS

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)

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.

  • Was this tutorial helpful?
  • YesNo
Next Oracle CURRENT_DATE

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Date Functions

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEXT_DAY
  • NEW_TIME
  • ROUND
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR
  • TO_DATE
  • TRUNC
  • TZ_OFFSET

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.