MONTHS_BETWEEN

Return the number of months between two dates, including a fractional part when days differ.

Category: datetimeReturns: DOUBLEDialect: Standard

Syntax

MONTHS_BETWEEN(date1, date2)

Description

## Overview Returns the number of months between date1 and date2 as a DOUBLE. If both dates fall on the same day-of-month, the result is a whole number. If the day-of-month differs, a fractional component is added based on a 31-day reference month. Special case: when date1 and date2 are both the last day of their respective months (even if those months have different lengths), the result is a whole number, matching the traditional MONTHS_BETWEEN convention. ## Behavior - Returns a DOUBLE. - Returns NULL if either argument is NULL. - Positive when date1 is later than date2, negative when earlier. - Whole number when the day-of-month matches in both inputs. - Whole number when both inputs are the last day of their respective months, even if those months have different lengths. - Otherwise, the fractional part is (day1 - day2) / 31. - Operates on DATE values; TIMESTAMP inputs are cast to DATE. ## Compatibility - Semantics matching the traditional MONTHS_BETWEEN convention: whole months plus a 31-day fractional remainder, with the last-day-of-month special case. Widely adopted in analytical SQL engines.

Parameters

Name	Type	Description
`date1`		Specifies the first (later) date. The result is computed as date1 minus date2.
`date2`		Specifies the second (earlier) date.

Examples

-- Same day-of-month yields a whole number
SELECT MONTHS_BETWEEN(DATE '2025-06-15', DATE '2025-01-15') AS months;

-- Different day-of-month yields a fractional result
SELECT MONTHS_BETWEEN(DATE '2025-03-20', DATE '2025-01-10') AS months;

-- Negative result when date1 is earlier
SELECT MONTHS_BETWEEN(DATE '2025-01-01', DATE '2025-06-01') AS months;

-- Subscription age in months, possibly fractional
SELECT user_id, MONTHS_BETWEEN(CURRENT_DATE(), signup_date) AS months_active
FROM billing.subscriptions.active_subs;

-- Round to whole months for banding
SELECT ROUND(MONTHS_BETWEEN(CURRENT_DATE(), hire_date)) AS months_tenure,
       COUNT(*) AS headcount
FROM workforce.hr.employees
GROUP BY months_tenure
ORDER BY months_tenure;

-- End-of-month inputs: both are last-of-month, so result is a whole number
SELECT MONTHS_BETWEEN(DATE '2025-02-28', DATE '2024-11-30') AS months;

Pitfalls

The fractional part uses a fixed 31-day denominator regardless of the actual month lengths. MONTHS_BETWEEN(DATE '2025-03-01', DATE '2025-02-01') returns approximately 0.9032, not 1.0, because March 1 and February 1 do not share a day-of-month and the 31-day denominator does not reflect February's 28 days.
The last-day-of-month special case can surprise: MONTHS_BETWEEN(DATE '2025-02-28', DATE '2025-01-31') returns 1.0 (both are last-of-month), while MONTHS_BETWEEN(DATE '2025-02-28', DATE '2025-01-28') also returns 1.0.
Round or truncate the result only when you explicitly want whole months; for month-boundary counting (not fractional age) use DATE_DIFF('month', ...) instead.
Negative results indicate date1 < date2. Wrap with ABS() if you only care about magnitude.