Return the number of whole completed years between two dates.
YEARS_BETWEEN(date1, date2)## Overview Returns the number of whole completed years between date1 and date2. An anniversary must have been reached for the year to count. Compared to simple calendar-year subtraction (EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2)), YEARS_BETWEEN correctly accounts for month-and-day within the year. Use this function for tenure calculations, age-in-years reporting, and any context where the number of completed anniversaries matters more than the count of calendar-year boundaries crossed. ## Behavior - Returns an INTEGER count of completed years. - Returns NULL if either argument is NULL. - Truncates toward zero: a partial year produces the integer part only. - Positive when date1 is after date2, negative when before, zero when the anniversary has not been reached in the same year. - Works on DATE values; TIMESTAMP inputs are cast to DATE. - Differs from DATE_DIFF('year', ...) which counts calendar-year boundaries crossed. ## Compatibility - Matches the 'completed years' convention used for age-in-years calculations. Equivalent to FLOOR(MONTHS_BETWEEN(date1, date2) / 12) with the anniversary edge case handled correctly.
| Name | Type | Description |
|---|---|---|
date1 | Specifies the first (later) date. The result is computed as date1 minus date2. | |
date2 | Specifies the second (earlier) date. |
-- Exactly five whole years
SELECT YEARS_BETWEEN(DATE '2025-06-15', DATE '2020-06-15') AS years;-- Partial year is truncated down
SELECT YEARS_BETWEEN(DATE '2025-06-15', DATE '2020-12-01') AS years;-- Negative when date1 is earlier
SELECT YEARS_BETWEEN(DATE '2020-01-01', DATE '2025-01-01') AS years;-- Compute employee tenure in whole years
SELECT employee_id, YEARS_BETWEEN(CURRENT_DATE(), hire_date) AS years_employed
FROM workforce.hr.employees;-- Bucket customers by tenure in years
SELECT YEARS_BETWEEN(CURRENT_DATE(), signup_date) AS tenure_years,
COUNT(*) AS customers
FROM commerce.customers.segments
GROUP BY tenure_years
ORDER BY tenure_years;-- Edge case: one day before anniversary returns 4, not 5
SELECT YEARS_BETWEEN(DATE '2025-06-14', DATE '2020-06-15') AS years;