Oracle ADD_MONTHS Function with Examples

In this article, I'll explain how the very useful ADD_MONTHS function works and use some examples.

Purpose of the Oracle ADD_MONTHS Function

The purpose of the Oracle ADD_MONTHS function is to add a specified number of months to a date and return another month.

Syntax

The syntax of the ADD_MONTHS function is:

1ADD_MONTHS ( input_date, number_months )

The function returns a DATE value.

Parameters

The parameters of the ADD_MONTHS function are:

• input_date (mandatory): This is the starting date, or the date that you will add a number of months to.
• number_months (mandatory): This is an integer value that represents the number of months to add to input_date.

Some things to note about this function:

• The input_date can be any DATETIME value, or a value that can be converted to DATE (such as a VARCHAR2 formatted as a date).
• The number_months can be any integer value, or a value that can be converted to an integer (such as a VARCHAR2 formatted as a whole number).
• The return type is always DATE, even if the data type supplied for input_date is not a DATE.

What If The Number Of Days Are Different?

This is a valid question. What would happen if you try to add to a month using ADD_MONTHS, with the original date being at the end of the month, and the resulting month doesn't have that day?

Well, the function would return the last day of the resulting month.

For example, adding 3 months to January 31 will return April 30 (because April only has 30 days).

Is There An Oracle ADD_DAYS Function Like ADD_MONTHS?

No, there is no ADD_DAYS function in Oracle.

However, you can easily add or subtract a day from a date by just adding the number to it.

For example, to add 3 days to today's date:

1SELECT SYSDATE + 1 FROM dual;

To subtract 7 days from today's date:

1SELECT SYSDATE - 7 FROM dual;

There doesn't need to be an extra function to do this.

Is There An Oracle ADD_YEARS Function Like ADD_MONTHS?

No, there is also no ADD_YEARS function in Oracle.

The good news is you can use ADD_MONTHS with a number_months parameter of 12, to add a single year. You can use multiples of 12 to add more years.

Also, you can use negative numbers to subtract years (such as -12).

See the Examples section below for more details.

Can Oracle ADD_MONTHS Subtract Months?

Yes, you can subtract months using the ADD_MONTHS function by simply using a negative number as the number_months parameter.

See the Examples section below for more details.

How Do You Get The First Day Of The Month Using Oracle ADD_MONTHS?

There are several ways to get the first day of the month, and one is using ADD_MONTHS.

1SELECT TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1) FROM dual;

You can also use the TRUNC function by itself:

1SELECT TRUNC(SYSDATE,'MM') FROM dual;

Examples of the Oracle ADD_MONTHS Function

Here are some examples of the ADD_MONTHS function.

Example 1

This example shows how to add one month to the current date.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 1) as ADD_MONTH_RESULT
3FROM dual;

Result:

The ADD_MONTH_RESULT shows that one month has been added to the date.

Example 2

This example shows how to add six months to the current date.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 6) as ADD_MONTH_RESULT
3FROM dual;

Result:

The result is six months added to the current date, which results in Feb 6, 2023.

Example 3

This example shows how to add a larger number of months.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 41) as ADD_MONTH_RESULT
3FROM dual;

Result

The result is in January 2026, which is 41 months into the future.

Example 4

This example shows how to subtract months by using a negative number

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, -6) as ADD_MONTH_RESULT
3FROM dual;

Result

The result is in February 2022, which is 6 months before the SYSDATE.

Example 5

This example shows the use of a number inside quotes, which is actually a CHAR but should get treated like a numeric value.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, '5') as ADD_MONTH_RESULT
3FROM dual;

Result:

The result is a date in January 2023, which is five months into the future.

Example 6

This example shows a text value inside the number_months parameter.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 'Six') as ADD_MONTH_RESULT
3FROM dual;

Result:

1ORA-01722: invalid number
201722. 00000 -  "invalid number"

This is because I used the value of "Six", which isn't actually a number.

Example 7

This example uses a specific date as the input date, instead of SYSDATE.

1SELECT TO_DATE('08-JUL-2015') as INPUT_DATE,
2ADD_MONTHS(TO_DATE('08-JUL-2015'), 1) as ADD_MONTH_RESULT
3FROM dual;

Result:

As you can see, a single month is added to the specified date.

Example 8

This example uses a decimal number as the number of months to add.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 2.3) as ADD_MONTH_RESULT_LOW,
3ADD_MONTHS(SYSDATE, 2.9) as ADD_MONTH_RESULT_HIGH
4FROM dual;

Result:

In both cases, the value is the same. This is because the decimal value has been rounded down to 2, even if the 2.9 would normally be rounded up to 3.

Example 9

This example shows how you can add a year to a date.

1SELECT SYSDATE,
2ADD_MONTHS(SYSDATE, 12) as ADD_MONTH_RESULT
3FROM dual;

Result:

The result is a date one year into the future.

Example 10

This example shows how to find the first day of the month using the ADD_MONTHS function.

1SELECT SYSDATE,
2TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)) as FIRST_DAY
3FROM dual;

Result:

As you can see, the result is the first day of the month that was provided.

Similar Functions

Some functions which are similar to the ADD_MONTHS function are:

• TRUNC - Truncates a datetime into a date format.
• LAST_DAY - Returns the date of the last day of the specified month.

You can find a full list of Oracle SQL functions here.