Calculate Age in Years from a Birthdate in SQL (per Dialect)

“Age” means completed years — someone born Dec 31 isn’t a year older until Dec 31. A plain year subtraction or boundary-counting DATEDIFF gets this wrong.

Correct full-years age

-- MySQL  (TIMESTAMPDIFF counts full years correctly)
SELECT TIMESTAMPDIFF(YEAR, birth, CURDATE());

-- PostgreSQL  (age() then take the year part)
SELECT EXTRACT(YEAR FROM age(CURRENT_DATE, birth));

-- SQLite
SELECT CAST(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', birth) AS INT);

-- DuckDB
SELECT date_part('year', age(CURRENT_DATE, birth));

BigQuery & Snowflake — correct the boundary

DATE_DIFF(..., YEAR) / DATEDIFF(year, …) count year boundaries, so subtract 1 if this year’s birthday hasn’t happened yet:

-- BigQuery
SELECT DATE_DIFF(CURRENT_DATE(), birth, YEAR)
     - IF(FORMAT_DATE('%m%d', CURRENT_DATE()) < FORMAT_DATE('%m%d', birth), 1, 0);

-- Snowflake
SELECT DATEDIFF(year, birth, CURRENT_DATE)
     - IFF(TO_CHAR(CURRENT_DATE, 'MMDD') < TO_CHAR(birth, 'MMDD'), 1, 0);

Why the correction: DATEDIFF(year, '2000-12-31', '2025-01-01') returns 25 because one year boundary (the calendar year) was crossed — but the person is 24. The %m%d/MMDD comparison (“has the birthday passed this year?”) subtracts the extra year. MySQL TIMESTAMPDIFF(YEAR, …) and Postgres age() already do full years, so they need no correction. The SQLite trick compares YYYY.MMDD numerically, which yields the same full-year result.