How to Use add_months in Oracle

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

In Oracle, add_months() function is used to return date with specified months added for the future date and if specified months is negative value then subtracted for past date.

Syntax

Add_Months(date, number_of_months);

Add_Months Function Example

1. To get Future Date, use add_months function with the positive number of months value.

SELECT ADD_MONTHS (SYSDATE, 5) FROM DUAL;

Output

ADD_MONTHS(SYSDATE,5)
---------------------
12-DEC-18 
1 row selected.

The current date is 12th July 18, so after adding 5 months the result is 12th Dec 18.

2. To get past date, specify the number of months in the negative value.

SELECT ADD_MONTHS (SYSDATE, -5) FROM DUAL;

Output

ADD_MONTHS(SYSDATE,-5)
----------------------
12-FEB-18 
1 row selected.

See also:

  1. Get Previous Months Data using Add_Months function
  2. Get Number of days between two dates
  3. Finding difference between two date parts