Menu

Oracle ADD_MONTHS Function: Month-End Rules & Examples

Oracle ADD_MONTHS(date, integer) returns a date after applying an integer month offset. Its month-end rule can change the day of the month, so ADD_MONTHS is not always a simple shift by the same day number. See the Oracle SQL Language Reference.

Syntax

ADD_MONTHS(date, integer)

Parameters and return type

  • date is a datetime value or a value Oracle can implicitly convert to DATE. Use an ANSI date literal such as DATE '2023-02-11' when writing a fixed date, rather than depending on the session’s NLS_DATE_FORMAT.
  • integer is the number of months to add. Positive values move forward; negative values move backward. Oracle can implicitly convert a compatible value to an integer.
  • The return type is always DATE, even when the input is another datetime type.
  • If either argument is NULL, the result is NULL.

Oracle defines a month according to the session parameter NLS_CALENDAR; the default calendar is Gregorian. The examples below assume that default.

Month-end behavior

If date is the last day of its month, ADD_MONTHS returns the last day of the target month. It also returns the target month’s last day when that month has fewer days than the input day number. Otherwise, the day number stays the same.

SELECT
    TO_CHAR(ADD_MONTHS(DATE '2023-01-31', 1), 'YYYY-MM-DD') AS from_month_end,
    TO_CHAR(ADD_MONTHS(DATE '2023-01-30', 1), 'YYYY-MM-DD') AS shorter_target_month,
    TO_CHAR(ADD_MONTHS(DATE '2023-02-11', 5), 'YYYY-MM-DD') AS same_day_number
FROM dual;

FROM_MONTH_END  SHORTER_TARGET_MONTH  SAME_DAY_NUMBER
--------------  --------------------  ---------------
2023-02-28      2023-02-28            2023-07-11

Use LAST_DAY when you need the last day of a month explicitly. For example, this expression returns the end of the month after January 2023:

SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(DATE '2023-01-15'), 1), 'YYYY-MM-DD') AS next_month_end
FROM dual;

NEXT_MONTH_END
--------------
2023-02-28

Add or subtract months

Pass a positive integer to move forward and a negative integer to move backward:

SELECT
    TO_CHAR(ADD_MONTHS(DATE '2023-02-11', 5), 'YYYY-MM-DD') AS plus_five_months,
    TO_CHAR(ADD_MONTHS(DATE '2023-02-11', -5), 'YYYY-MM-DD') AS minus_five_months
FROM dual;

PLUS_FIVE_MONTHS  MINUS_FIVE_MONTHS
----------------  -----------------
2023-07-11        2022-09-11

To calculate an offset from the current date, use CURRENT_DATE. Its value depends on the session time zone, so the result changes over time.

NULL arguments

If either argument is NULL, ADD_MONTHS returns NULL:

SET NULL 'NULL';
SELECT
    ADD_MONTHS(NULL, 5) AS null_date,
    ADD_MONTHS(DATE '2022-09-11', NULL) AS null_months
FROM dual;

NULL_DATE  NULL_MONTHS
---------  -----------
NULL       NULL

SET NULL 'NULL' changes how SQL*Plus displays SQL NULL values.

For the number of months between two dates, see MONTHS_BETWEEN.