SQL Reference ยท Date and Time Functions
SQL EXTRACT() and DATE_PART() functions
Reference for SQL EXTRACT() and DATE_PART(): get the year, month, day, hour or weekday from a date, with the SQLite STRFTIME() equivalent and dialect differences.
EXTRACT(field FROM source) is the standard SQL way to pull one component out of a date or timestamp: the year, the month, the day, the hour, the weekday and more. PostgreSQL also offers DATE_PART('field', source) with the same meaning, and MySQL and SQL Server have shortcut functions such as YEAR() and MONTH().
SQLite has no EXTRACT(). The equivalent there is STRFTIME() with a format code, wrapped in CAST(... AS INTEGER) to get a number. The runnable examples below use that form; the standard syntax is shown as read-only code.
-- PostgreSQL, MySQL, Oracle SELECT EXTRACT(YEAR FROM hire_date) FROM employees; -- SQLite SELECT CAST(STRFTIME('%Y', hire_date) AS INTEGER) FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
EXTRACT(field FROM source) DATE_PART('field', source) -- PostgreSQL
| Name | Description |
|---|---|
field |
The component: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND; PostgreSQL and MySQL add more such as QUARTER, WEEK, DOW, DOY and EPOCH. |
source |
A date, time or timestamp expression. |
Returns: a number. The numeric type varies (PostgreSQL 14+ returns numeric, older versions double precision). NULL in gives NULL out.
SQLite equivalents:
| Part | SQLite expression |
|---|---|
| Year, month, day | CAST(STRFTIME('%Y', d) AS INTEGER), '%m', '%d' |
| Hour, minute, second | '%H', '%M', '%S' |
| Weekday | '%w' (0 = Sunday) or '%u' (1 = Monday) |
| Day of year | '%j' |
| Quarter | (CAST(STRFTIME('%m', d) AS INTEGER) + 2) / 3 |
| Unix epoch | UNIXEPOCH(d) |
Dialect differences
| Database | Year of a date | Weekday |
|---|---|---|
| SQLite | CAST(STRFTIME('%Y', d) AS INTEGER) |
STRFTIME('%w', d) |
| PostgreSQL | EXTRACT(YEAR FROM d), DATE_PART('year', d) |
EXTRACT(DOW FROM d) (0 = Sunday), ISODOW (1 = Monday) |
| MySQL | EXTRACT(YEAR FROM d), YEAR(d) |
WEEKDAY(d) (0 = Monday), DAYOFWEEK(d) (1 = Sunday) |
| SQL Server | DATEPART(year, d), YEAR(d) (no EXTRACT) |
DATEPART(weekday, d), depends on SET DATEFIRST |
| Oracle | EXTRACT(YEAR FROM d) (for a plain DATE, only year, month and day; use a timestamp for time parts) |
TO_CHAR(d, 'D') |
-- MySQL SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date) FROM employees; -- SQL Server SELECT DATEPART(quarter, hire_date), DATEPART(week, hire_date) FROM employees;
Usage notes
- Weekday numbering is not portable: 0 is Sunday in PostgreSQL's
DOWand SQLite's%w, but Monday in MySQL'sWEEKDAY(). Check before comparing across systems. - Grouping by part loses the other parts: grouping on the month alone merges January of every year. Group by year and month together, or by
STRFTIME('%Y-%m', d). - Indexes.
WHERE EXTRACT(YEAR FROM d) = 2025cannot use a plain index ond; preferd >= '2025-01-01' AND d < '2026-01-01'. - Time zones. In databases with zone-aware timestamps the extracted hour is in the session's time zone.
- Strings without CAST. In SQLite
STRFTIMEreturns text such as'02'; cast before comparing with numbers or sorting numerically. - Week numbers have several definitions (ISO, US, first-Monday); name the one you need.