ADD_MONTHS

The add_months() function adds a specified number of months to a given date or timestamp.

If the input date is month-end or exceeds the resulting month’s days, the result is adjusted to the last day of the new month. Otherwise, the original day is preserved.

Syntax

ADD_MONTHS(<date_or_timestamp>, <number_of_months>)

Return Type

Returns a TIMESTAMP or DATE type

Examples

Basic Month Addition

SELECT ADD_MONTHS('2023-01-15'::DATE, 3);
├───────────────────────────────────┤
│ 2023-04-15                        │
╰───────────────────────────────────╯

Subtracting Months

SELECT ADD_MONTHS('2023-06-20'::DATE, -4);
├─────────────────────────────────────┤
│ 2023-02-20                          │
╰─────────────────────────────────────╯

Month-End Adjustment

SELECT ADD_MONTHS('2023-01-31'::DATE, 1);
├───────────────────────────────────┤
│ 2023-02-28                        │
╰───────────────────────────────────╯

With Timestamp Preservation

SELECT ADD_MONTHS('2023-03-15 14:30:00'::TIMESTAMP, 5);
├─────────────────────────────────────────────────┤
│ 2023-08-15 14:30:00.000000                      │
╰─────────────────────────────────────────────────╯

With last day of month

CREATE TABLE contracts (
    id INT,
    sign_date DATE,
    duration_months INT
);

INSERT INTO contracts VALUES
    (1, '2023-01-15', 12),
    (2, '2024-02-28', 6),
    (3, '2023-11-30', 3);

SELECT
    id,
    sign_date,
    ADD_MONTHS(sign_date, duration_months) AS end_date
FROM contracts;
├─────────────────┼────────────────┼────────────────┤
│               1 │ 2023-01-15     │ 2024-01-15     │
│               2 │ 2024-02-28     │ 2024-08-28     │
│               3 │ 2023-11-30     │ 2024-02-29     │
╰───────────────────────────────────────────────────╯

See Also

• DATE_ADD: Alternative function for adding specific time intervals
• DATE_SUB: Function for subtracting time intervals