SQL EXTRACT vs DATEPART: Date Part Extraction Across Dialects

By Sharon Ben-Moshe · October 7, 2026 · 1 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Extract year, month, day, and hour across SQL dialects: ANSI EXTRACT, SQL Server DATEPART, MySQL date helpers, and SQLite strftime.

Quick answer

ANSI standard SQL uses EXTRACT(field FROM source) to extract temporal components like YEAR, MONTH, DAY, and HOUR. PostgreSQL, BigQuery, and MySQL support EXTRACT. Microsoft SQL Server uses DATEPART(part, date) or DATENAME(part, date), while SQLite uses strftime('%format', date).

FAQs

Is EXTRACT ANSI SQL compliant?

Yes, EXTRACT is part of the SQL-92 standard and is portable across PostgreSQL, MySQL, Oracle, and Google BigQuery.

What is the difference between DATEPART and DATENAME in SQL Server?

DATEPART returns the date part as an integer (e.g. 5 for May). DATENAME returns the localized name as a string (e.g. 'May' or 'Monday').

How do I extract the day of the week in PostgreSQL?

Use `EXTRACT(DOW FROM timestamp)` where 0 is Sunday, or `EXTRACT(ISODOW FROM timestamp)` where 1 is Monday and 7 is Sunday.

Why does filtering with EXTRACT in WHERE cause slow queries?

Wrapping an indexed timestamp column in `EXTRACT(YEAR FROM created_at) = 2026` is non-SARGable. It forces the database to evaluate the function for every row via a table scan rather than doing an index range seek.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

ANSI SQL: EXTRACT(YEAR FROM order_date) works across PostgreSQL, MySQL, and BigQuery.

SQL Server: DATEPART(year, order_date) returns integers; DATENAME(month, order_date) returns names.

SQLite: strftime('%Y', order_date) returns formatted string values.

Avoid EXTRACT in WHERE clauses on indexed columns; use range comparisons instead for query performance.

Cross-dialect date extraction reference

Here is how to extract Year and Month across the major database systems:

Extracting date parts across 5 engines

-- PostgreSQL & Google BigQuery
SELECT
  EXTRACT(YEAR FROM created_at) AS order_year,
  EXTRACT(MONTH FROM created_at) AS order_month
FROM orders;

-- SQL Server (T-SQL)
SELECT
  DATEPART(year, created_at) AS order_year,
  DATENAME(month, created_at) AS order_month_name
FROM orders;

-- MySQL
SELECT
  YEAR(created_at) AS order_year,
  MONTH(created_at) AS order_month
FROM orders;

-- SQLite
SELECT
  CAST(strftime('%Y', created_at) AS INTEGER) AS order_year,
  CAST(strftime('%m', created_at) AS INTEGER) AS order_month
FROM orders;

Performance warning: Non-SARGable date filtering

Applying EXTRACT to columns in WHERE clauses prevents the database optimizer from using indexes:

Index-friendly date filtering vs non-SARGable function call

-- SLOW: Non-SARGable (Forces full table scan over millions of rows)
SELECT * FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2026;

-- FAST: SARGable index range seek
SELECT * FROM sales
WHERE sale_date >= '2026-01-01'
  AND sale_date <  '2027-01-01';

SQL EXTRACT vs DATEPART: Date Part Extraction Across Dialects

Quick answer

FAQs

Is EXTRACT ANSI SQL compliant?

What is the difference between DATEPART and DATENAME in SQL Server?

How do I extract the day of the week in PostgreSQL?

Why does filtering with EXTRACT in WHERE cause slow queries?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Cross-dialect date extraction reference

Performance warning: Non-SARGable date filtering

Official references

Quick answer

FAQs

Is EXTRACT ANSI SQL compliant?

What is the difference between DATEPART and DATENAME in SQL Server?

How do I extract the day of the week in PostgreSQL?

Why does filtering with EXTRACT in WHERE cause slow queries?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Cross-dialect date extraction reference

Performance warning: Non-SARGable date filtering

Related SQL guides

Official references