Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- PostgreSQL, MySQL, Oracle
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;

-- SQLite
SELECT CAST(STRFTIME('%Y', hire_date) AS INTEGER) FROM employees;

Try it

SQL Hiring per year in SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL All the usual parts of one timestamp

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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')
SQL
-- 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 DOW and SQLite's %w, but Monday in MySQL's WEEKDAY(). 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) = 2025 cannot use a plain index on d; prefer d >= '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 STRFTIME returns 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.