EXTRACT / DATEPARTFunctions
Buy Me A Coffee

Extract a specific component (year, month, day, hour, etc.) from a date or timestamp as a numeric value.

Pulls a specific component (year, month, day, hour, etc.) from a date or timestamp value. EXTRACT is ANSI SQL standard; DATE_PART is PostgreSQL-specific. Both are essential for grouping by time periods or parsing temporal data.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all EXTRACT(unit FROM date) is SQL-standard and supported. Also YEAR(date), MONTH(date), DAY(date), HOUR(time), MINUTE(time), SECOND(time) as shorthand functions. DAYOFWEEK() returns 1=Sunday, WEEKDAY() returns 0=Monday.
PostgreSQL ✓ Supported all EXTRACT(field FROM source) returns a float8. DATE_PART(field, source) is equivalent. Fields include: year, month, day, hour, minute, second, milliseconds, epoch, dow (0=Sun), isodow (1=Mon), week, quarter, timezone.
SQL Server ✓ Supported all DATEPART(datepart, date) returns an integer. YEAR(date), MONTH(date), DAY(date) are shorthand. Parts include: year, quarter, month, dayofyear, day, weekday, week, hour, minute, second, millisecond, microsecond. DATEPART(weekday, date) is locale-dependent (SET DATEFIRST).
Oracle ✓ Supported all EXTRACT(field FROM date). Fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. TO_CHAR(date, 'format') is the traditional Oracle approach for formatting.
SQLite ✗ Not Supported — No EXTRACT() or DATEPART(). Use strftime() with format codes: %Y=year, %m=month, %d=day, %H=hour, %M=minute, %S=second, %w=weekday (0=Sun), %W=week number.

Details

SQL Server uses DATEPART() instead of EXTRACT() — they're equivalent but different names. PostgreSQL's EXTRACT(EPOCH FROM ts) to get a Unix timestamp is uniquely useful. SQLite has no EXTRACT at all; strftime() with a CAST is the workaround. Week-of-year and day-of-week numbering differs between engines (ISO vs locale-based) — always test if you're using those.

Standard Syntax

SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Not supported

Per-Database Syntax & Notes

MySQL

EXTRACT(unit FROM date) is SQL-standard and supported. Also YEAR(date), MONTH(date), DAY(date), HOUR(time), MINUTE(time), SECOND(time) as shorthand functions. DAYOFWEEK() returns 1=Sunday, WEEKDAY() returns 0=Monday.

SELECT EXTRACT(YEAR FROM NOW()); -- 2024 SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()); SELECT DAYOFWEEK(NOW()); -- 1=Sun, 7=Sat SELECT WEEK(NOW()); -- ISO week number

PostgreSQL

EXTRACT(field FROM source) returns a float8. DATE_PART(field, source) is equivalent. Fields include: year, month, day, hour, minute, second, milliseconds, epoch, dow (0=Sun), isodow (1=Mon), week, quarter, timezone.

SELECT EXTRACT(YEAR FROM NOW()); -- 2024 SELECT EXTRACT(DOW FROM NOW()); -- 0=Sun, 6=Sat SELECT EXTRACT(ISODOW FROM NOW()); -- 1=Mon, 7=Sun (ISO) SELECT EXTRACT(EPOCH FROM NOW()); -- Unix timestamp SELECT DATE_PART('month', NOW()); -- same as EXTRACT

SQL Server

DATEPART(datepart, date) returns an integer. YEAR(date), MONTH(date), DAY(date) are shorthand. Parts include: year, quarter, month, dayofyear, day, weekday, week, hour, minute, second, millisecond, microsecond. DATEPART(weekday, date) is locale-dependent (SET DATEFIRST).

SELECT DATEPART(YEAR, GETDATE()); -- 2024 SELECT DATEPART(QUARTER, GETDATE()); -- 1-4 SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()); SELECT DATEPART(WEEKDAY, GETDATE()); -- locale-dependent

Oracle

EXTRACT(field FROM date). Fields: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. TO_CHAR(date, 'format') is the traditional Oracle approach for formatting.

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; -- 2024 SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; -- 1-12 SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; -- quarter (1-4) SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL; -- ISO week number

SQLite

No EXTRACT() or DATEPART(). Use strftime() with format codes: %Y=year, %m=month, %d=day, %H=hour, %M=minute, %S=second, %w=weekday (0=Sun), %W=week number.

SELECT CAST(strftime('%Y', 'now') AS INTEGER); -- year SELECT CAST(strftime('%m', 'now') AS INTEGER); -- month SELECT strftime('%w', 'now'); -- 0=Sun, 6=Sat