EXTRACT()
SQL · Date Functions
Theory
EXTRACT pulls one field out of a date or timestamp and returns it as a number. EXTRACT(YEAR FROM hire_date) on 2023-07-01 gives 2023, EXTRACT(MONTH FROM ...) gives 7 and EXTRACT(DAY FROM ...) gives 1. Common fields are YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, and in many databases DOW (day of week) and WEEK.
EXTRACT is standard SQL and works in PostgreSQL, MySQL, Oracle, Snowflake and BigQuery. SQL Server instead uses DATEPART(year, hire_date) or the shortcuts YEAR(), MONTH() and DAY(). SQLite has no EXTRACT; you use strftime('%Y', hire_date), which returns text, so wrap it in CAST(... AS INTEGER) when you need a number. Typical uses are grouping sales by year or month, finding everyone hired in a given year, or spotting weekend orders.
Why we use it
Dates are stored as one value, but questions are often about a part of it: 'how many people joined each year?' or 'which month sells most?'. EXTRACT breaks the date apart so you can group, filter or compare on that part.
When to use it
Use it to group or label by year, month or weekday. For filtering on a big table, a date range (hire_date >= '2024-01-01' AND hire_date < '2025-01-01') is faster than EXTRACT(YEAR ...) = 2024, because it can use an index.
How it works
- Read a date value, e.g. 2023-07-01
- Choose the field: YEAR, MONTH, DAY, HOUR...
- Database decodes the date into its parts
- Return the chosen part as a number (2023, 7, 1)
- Used in GROUP BY or WHERE? Yes: Group or filter by that part. No: Show it as a column.
- Date part ready for analysis
Syntax
EXTRACT(field FROM date)Example
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;Key points
- Standard SQL: PostgreSQL, MySQL, Oracle, Snowflake and BigQuery support it
- SQL Server uses DATEPART(year, d) or YEAR(d)/MONTH(d)/DAY(d) instead
- SQLite uses strftime('%Y', d) — returns text, so CAST it to INTEGER
- Returns a number, not a date — 7 for July, not '2026-07-01'
- Filtering with EXTRACT on an indexed column usually blocks the index; use a date range