EXTRACT()
Pulls a single date part (year, month, day, ...) out of a date or timestamp.
Description
EXTRACT returns one component of a date or timestamp as a number — the year,
month, day, hour, and so on. It's the SQL-standard way to break a date into
parts for filtering or grouping, and it reads as EXTRACT(field FROM source).
Syntax
EXTRACT(YEAR FROM source)
EXTRACT(MONTH FROM source)
EXTRACT(DAY FROM source)Parameters
| Name | Description | Optional |
|---|---|---|
| field | The part to extract (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW, DOY). | No |
| source | The date/timestamp to read. | No |
Return Type
Returns an INTEGER (or DOUBLE for fractional sub-day parts).
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
EXTRACT vs DATE_TRUNC
EXTRACT gives you the number; DATE_TRUNC gives you a timestamp pinned to
the unit start. Use EXTRACT when you need the value, not a date.
Common Mistakes
- Argument order. It's
EXTRACT(field FROM source), not(source, field). - DOW vs DOY.
DOWis day of week;DOYis day of year — easy to mix up. - Stringly dates. The source must be a real date/timestamp type.
Related Functions
See also: DATE_TRUNC, DATEDIFF, NOW.