Oracle ADD_MONTHS Function: A Simple Guide

Working with dates in SQL can be tricky, especially when you need to add or subtract months. Oracle provides a powerful and straightforward function for this exact task: ADD_MONTHS.

This simple tutorial will show you exactly how to use it, with clear examples to get you started.

What Does ADD_MONTHS Do?

The ADD_MONTHS function returns a new date by adding a specific number of months to a starting date. You can also use a negative number to subtract months, allowing you to easily calculate dates in the future or the past.

It's perfect for tasks like:

• Finding a subscription renewal date.
• Calculating a loan maturity date.
• Determining a date 6 months in the past for a report.

Syntax

The syntax for the ADD_MONTHS function is very simple:

ADD_MONTHS(start_date, number_of_months)

Parameters:

• start_date: This is your initial date. It can be a DATE value, a column (like hire_date), or any value that Oracle can implicitly convert to a DATE.
• number_of_months: This is the integer representing the number of months you want to add.
  • A positive number (like 6) moves the date forward.
  • A negative number (like -3) moves the date backward.

The "Last Day of the Month" Rule

Here's the most important rule to remember:

If your start_date is the last day of the month (like Jan 31 or Feb 28), ADD_MONTHS will always return the last day of the resulting month.

For example, adding 1 month to January 31st gives February 28th (or 29th in a leap year), not February 31st (which doesn't exist). This intelligent handling prevents errors and makes date logic much more reliable.

Examples

Let's look at two practical examples. We'll use the DUAL table, which is a special one-row, one-column table in Oracle used for testing functions.

Example 1: Adding Months to a Specific Date (Going Forward) with ADD_MONTHS

Let's find the date that is 4 months after October 15th, 2024.

Query:

We use TO_DATE to convert our string into a proper DATE format that ADD_MONTHS can use.

SELECT 
  TO_DATE('15-OCT-2024', 'DD-MON-YYYY') AS "Start Date",
  ADD_MONTHS(TO_DATE('15-OCT-2024', 'DD-MON-YYYY'), 4) AS "Future Date"
FROM 
  DUAL;

Result:

Example 2: Subtracting Months (and seeing the "Last Day" rule) using ADD_MONTHS

Now, let's find the date 1 month before March 31st, 2024 (a leap year). This will show how the "last day" rule works.

Query:

We provide a negative number (-1) to subtract one month.

SELECT 
  TO_DATE('31-MAR-2024', 'DD-MON-YYYY') AS "Start Date",
  ADD_MONTHS(TO_DATE('31-MAR-2024', 'DD-MON-YYYY'), -1) AS "Past Date"
FROM 
  DUAL;

Result:

Notice the result is February 29th, not February 31st or March 1st. Because our start date (Mar 31) was the last day of its month, the function correctly returned the last day of the resulting month (Feb 29).

Summary

The ADD_MONTHS function is the simplest and most reliable way to add or subtract months from a date in Oracle SQL. Just provide your date and the number of months, and remember its smart handling of month-end dates.