ADD_MONTHS

Add (or subtract) a number of months to a date, clamping to the end of the month when needed.

Category: datetimeReturns: DATEDialect: Standard

Syntax

ADD_MONTHS(date, months)

Description

## Overview Adds a signed number of months to a DATE and returns the resulting DATE. Negative month counts subtract; zero returns the input date unchanged. The function is designed for calendar-aware arithmetic where 'add one month' should mean 'move to the same day-of-month in the next month', not 'add 30 days'. Use ADD_MONTHS for subscription renewals, billing cycles, fiscal-period arithmetic, and any anniversary-style calculation where month boundaries matter more than day counts. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - End-of-month clamping: if the target month has fewer days than the source day-of-month, the result is set to the last day of the target month. Example: ADD_MONTHS('2025-01-31', 1) returns 2025-02-28. - ADD_MONTHS(date, 0) returns the input date unchanged. - Negative counts subtract and follow the same clamping rule. - The month parameter is an INT; overflow of INT is not protected against, so extreme values can wrap. - Time components are not involved: the input is a DATE, the output is a DATE. ## Compatibility - Matches the widely used ADD_MONTHS convention: same day-of-month in the target month with end-of-month clamping. Adopted across analytical SQL engines.

Parameters

Name	Type	Description
`date`		Specifies the starting date to which months are added.
`months`		Specifies the number of months to add. Accepts negative values to subtract months. Fractional values are not accepted; cast to INT first.

Examples

-- Add three months to a fixed date
SELECT ADD_MONTHS(DATE '2025-01-15', 3) AS result;

-- Subtract two months by passing a negative value
SELECT ADD_MONTHS(DATE '2025-06-10', -2) AS result;

-- End-of-month clamp: January 31 plus 1 month lands on February 28
SELECT ADD_MONTHS(DATE '2025-01-31', 1) AS result;

-- Leap-year handling: February 29 plus 12 months lands on February 28
SELECT ADD_MONTHS(DATE '2024-02-29', 12) AS result;

-- Derive a subscription renewal date six months out
SELECT subscription_id, ADD_MONTHS(start_date, 6) AS renewal_date
FROM billing.subscriptions.active_subs;

-- Roll anniversary forward by a column-driven month count
SELECT employee_id, ADD_MONTHS(hire_date, tenure_months) AS anniversary
FROM workforce.hr.employees;

Pitfalls

ADD_MONTHS clamps to the end of the target month rather than overflowing into the next month. ADD_MONTHS('2025-01-31', 1) returns February 28, not March 3. This differs from naive 30-day addition.
Round-tripping does not preserve the original day: ADD_MONTHS(ADD_MONTHS(DATE '2025-01-31', 1), -1) returns 2025-01-28, not 2025-01-31, because the first call clamped the day.
The function operates on DATE, not TIMESTAMP. If you pass a TIMESTAMP the engine will cast it down to DATE and discard the time-of-day; to preserve time-of-day use arithmetic with INTERVAL '1 month' on a TIMESTAMP expression.
Leap-year February 29 anniversaries land on February 28 in non-leap years. Applications that care about leap-day anniversaries must special-case this explicitly.