Recipe

Extract year, month, or day from a date

Short answer: EXTRACT(YEAR FROM date_col). The same function works for MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of year), QUARTER, WEEK, and a few others. ANSI standard, supported on Postgres, MySQL, Oracle, DuckDB, BigQuery, Snowflake, Redshift. SQLite and SQL Server have no native EXTRACT: use strftime() (SQLite) or DATEPART / YEAR() / MONTH() (SQL Server). Synonym DATE_PART('unit', date_col) works on Postgres, DuckDB, Snowflake, Redshift.

Canonical SQL #

sql

-- The portable form (works almost everywhere).
SELECT
  EXTRACT(YEAR  FROM order_date) AS year,
  EXTRACT(MONTH FROM order_date) AS month,
  EXTRACT(DAY   FROM order_date) AS day
FROM orders;

-- Equivalent on Postgres / Snowflake / Redshift / DuckDB.
SELECT DATE_PART('year', order_date) AS year FROM orders;

Example #

Loading SQL editor...

Common variants #

sql

-- Group by year.
SELECT EXTRACT(YEAR FROM order_date) AS yr, COUNT(*)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

-- Group by year-month (preserves the year — the right form for time series).
SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Filter to a specific year.
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;

-- Faster on indexed columns: range filter instead of EXTRACT
-- (lets the engine use the index on order_date).
SELECT * FROM orders
WHERE order_date >= DATE '2026-01-01'
  AND order_date <  DATE '2027-01-01';

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL / DuckDB / Snowflake / Redshift	`EXTRACT(YEAR FROM col)` or `DATE_PART('year', col)`	both work
Oracle	`EXTRACT(YEAR FROM col)`	no `DATE_PART`; use `TO_CHAR(col, 'YYYY')` for string
MySQL	`EXTRACT(YEAR FROM col)` or `YEAR(col)`	shortcuts: `YEAR()`, `MONTH()`, `DAY()`, `DAYNAME()`
SQL Server	`YEAR(col)` / `DATEPART(year, col)`	no native `EXTRACT` (any version)
BigQuery	`EXTRACT(YEAR FROM col)`	no `DATE_PART`
SQLite	`strftime('%Y', col)`	returns string; `CAST` to `INTEGER` if needed

Postgres / DuckDB / Snowflake / Redshift: EXTRACT(YEAR FROM date_col) and DATE_PART('year', date_col) both work.
Oracle: EXTRACT(YEAR FROM date_col) only — no DATE_PART. For string output, TO_CHAR(date_col, 'YYYY').
MySQL: EXTRACT(YEAR FROM date_col) works. Also has shortcuts: YEAR(date_col), MONTH(date_col), DAY(date_col), DAYNAME(date_col), MONTHNAME(date_col).
SQL Server: no native EXTRACT (any version). Use YEAR(date_col), MONTH(date_col), DAY(date_col), or DATEPART(year, date_col).
SQLite: no native EXTRACT. Use strftime('%Y', date_col) (returns a string) or CAST(strftime('%Y', date_col) AS INTEGER).
BigQuery: EXTRACT(YEAR FROM date_col) is canonical. Also has DATETIME_DIFF, DATETIME_TRUNC for related operations.
Day-of-week numbering: EXTRACT(DOW FROM ...) returns 0-6 in Postgres/DuckDB starting Sunday; EXTRACT(ISODOW FROM ...) returns 1-7 starting Monday (Postgres). MySQL's DAYOFWEEK is 1-7 Sunday-first; WEEKDAY is 0-6 Monday-first. Always test before reporting.

Warning

WHERE EXTRACT(YEAR FROM order_date) = 2026 defeats indexes on order_date. Wrapping the column in a function forces the engine to scan and compute EXTRACT for every row. The fix: rewrite the predicate as a half-open range, WHERE order_date >= DATE '2026-01-01' AND order_date < DATE '2027-01-01'. Same logical result, the index is usable, and the query plan changes from a sequential scan to an index range scan. The performance gap on a large table is often two to three orders of magnitude.

Extract year, month, or day from a date

Canonical SQL #

Example #

Common variants #

Dialect comparison #

Related In Recipes