Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

Oracle ADD_MONTHS function

Add a month or months to a given date in Oracle

ADD_MONTHS() function returns a date with a given number of months added (date plus integer months). A month is defined by the session parameter NLS_CALENDAR.

Syntax: 

ADD_MONTHS(date, integer)

Parameters:

Name Description
date A datetime value or any value that can be implicitly converted to DATE.
integer An integer or any value that can be implicitly converted to an integer.

Return value type :

The return type is always DATE, regardless of the datatype of date.

Note: If the date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as a date.

Applies to :
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Pictorial Presentation of Oracle ADD_MONTHS function

Example: Oracle ADD_MONTHS() function

The following statement returns the hire date, month before and after the hire_date in the sample table employees :

Sample table: employees 


SQL> SELECT  hire_date, TO_CHAR(ADD_MONTHS(hire_date, -1), 'DD-MON-YYYY') "Previous month",
TO_CHAR(ADD_MONTHS(hire_date, 1), 'DD-MON-YYYY') "Next month"
FROM employees 
WHERE first_name = 'Lex';

Sample Output: 

HIRE_DATE Previous month       Next month
--------- -------------------- --------------------
13-JAN-01 13-DEC-2000          13-FEB-2001

Previous: Oracle Datetime Functions Introduction
Next: CURRENT_DATE