In Oracle Database, the ADD_MONTHS() function adds a given number of months to a date, and returns the result.

Syntax

The syntax goes like this:

ADD_MONTHS(date, integer)

Where date can be a datetime value or any value that can be implicitly converted to DATE.

The integer argument can be an integer or any value that can be implicitly converted to an integer.

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

Example

Here’s an example:

SELECT ADD_MONTHS(DATE '2020-01-01', 3)
FROM DUAL;

Result:

01/APR/20

This example displays the date based on the value of my system’s NLS_DATE_FORMAT parameter (which is currently DD/MON/RR). We can either change this parameter, or use a function like TO_CHAR() to return the result in a different format.

Example:

SELECT TO_CHAR(ADD_MONTHS(DATE '2020-01-01', 3), 'YYYY-MM-DD')
FROM DUAL;

Result:

2020-04-01

Subtract Months

To subtract months from a date, use a negative value for the second argument.

Example:

SELECT ADD_MONTHS(DATE '2020-01-01', -3)
FROM DUAL;

Result:

01/OCT/19

Passing Various Date Formats

The date can be provided in various formats, as long as it resolves to a date:

SELECT ADD_MONTHS('01 Jan 2020', 3)
FROM DUAL;

Result:

01/APR/20

But passing one that can’t be resolved results in an error:

SELECT ADD_MONTHS('Jan 01 2020', 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ADD_MONTHS('Jan 01 2020', 3)
FROM DUAL
Error report -
ORA-01858: a non-numeric character was found where a numeric was expected

However, this can depend on the value of the NLS_DATE_FORMAT parameter. If we change this parameter:

ALTER SESSION SET NLS_DATE_FORMAT = "Mon DD RR";
SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';

Result:

       VALUE 
____________ 
Mon DD RR   

And then run the query again:

SELECT ADD_MONTHS('Jan 01 2020', 3)
FROM DUAL;

Result:

Apr 01 20

We no longer get the error.

Null Arguments

Passing a date of null returns null:

SET NULL 'null';
SELECT ADD_MONTHS(null, 1)
FROM DUAL;

Result:

null

But passing null for the second argument results in an error:

SELECT ADD_MONTHS('2020-01-01', null)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ADD_MONTHS('2020-01-01', null)
FROM DUAL
Error report -
ORA-01843: not a valid month

Note that by default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. In the example above, I specified that the string null should be returned.

Invalid Number of Arguments

Passing an invalid number of arguments results in an error:

SELECT ADD_MONTHS(3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ADD_MONTHS(3)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: