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

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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook