Extract Year, Month or Day from a Date in SQL (EXTRACT per Dialect)

Get a single component out of a date — for grouping, filtering, or labels.

Year / month / day

-- Standard EXTRACT (Postgres, BigQuery, Snowflake, DuckDB, MySQL)
SELECT EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d), EXTRACT(DAY FROM d);

-- Shorthand functions (MySQL, Snowflake, DuckDB)
SELECT YEAR(d), MONTH(d), DAY(d);

-- PostgreSQL also has date_part()
SELECT date_part('year', d), date_part('month', d);

-- SQLite (strftime returns TEXT — cast to INT)
SELECT CAST(strftime('%Y', d) AS INT),
       CAST(strftime('%m', d) AS INT),
       CAST(strftime('%d', d) AS INT);

Quarter / hour

SELECT EXTRACT(QUARTER FROM d);         -- Postgres / BigQuery / Snowflake / DuckDB / MySQL
SELECT (CAST(strftime('%m', d) AS INT) + 2) / 3;   -- SQLite (no quarter token)

SELECT EXTRACT(HOUR FROM ts);           -- needs a timestamp, not a date

Gotchas: EXTRACT is the portable choice — it works everywhere here (MySQL added it long ago). SQLite returns text from strftime, so wrap it in CAST(... AS INT) before doing math or comparisons. SQLite has no quarter token — compute it from the month. EXTRACT(DOW …) (day of week) numbering varies by dialect — see the day-of-week recipe.