Oracle ADD_MONTHS() Function

Oracle ADD_MONTHS() is a built-in function used to add or subtract a specified number of months to a given date.

Oracle `ADD_MONTHS()` Syntax

Here is the syntax of the Oracle ADD_MONTHS() function:

ADD_MONTHS(date, months)

date: Required. The date to be modified. It can be a datetime value or any value that can be implicitly converted to a DATE.
months: Required. The number of months to add. If months is positive, it means adding months; if months is negative, it means subtracting months.

The Oracle ADD_MONTHS() function returns a new date that is the result of adding or subtracting the specified number of months to the given date.

If any of the parameters is NULL, ADD_MONTHS() will return NULL.

Here are some examples that demonstrate the usage of the Oracle ADD_MONTHS() function.

To add 5 months to the date 2023-02-11, use the following statement:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
    ADD_MONTHS('2023-02-11', 5)
FROM DUAL;

Output:

ADD_MONTHS('2023-02-11',5)
_____________________________
2023-07-11

Here, we use the ALTER SESSION statement to modify the date format of the current session to YYYY-MM-DD.

If you provide a negative number of months, the Oracle ADD_MONTHS() function will subtract the given number of months.

SELECT
    ADD_MONTHS('2023-02-11', -5)
FROM dual;

Output:

ADD_MONTHS('2023-02-11',-5)
______________________________
2022-09-11

If you need to add or subtract months from the current date, use CURRENT_DATE.

SELECT
    CURRENT_DATE,
    ADD_MONTHS(CURRENT_DATE, 5) "+5 months",
    ADD_MONTHS(CURRENT_DATE, -5) "-5 months"
FROM dual;

Output:

CURRENT_DATE    +5 months     -5 months
_______________ _____________ _____________
2023-02-11      2023-07-11    2022-09-11

If any of the parameters is NULL, ADD_MONTHS() will return NULL.

SET NULL 'NULL';
SELECT
    ADD_MONTHS(NULL, 5) NULL_1,
    ADD_MONTHS('2022-09-11', NULL) NULL_2,
    ADD_MONTHS(NULL, NULL) NULL_3
FROM dual;

Output:

NULL_1    NULL_2    NULL_3
_________ _________ _________
NULL      NULL      NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Oracle ADD_MONTHS() is a built-in function used to add or subtract a specified number of months to a given date.