SQL - DATEPART() / EXTRACT() - Get Part of Date

Key Insights

DATEPART() is SQL Server’s proprietary function while EXTRACT() follows the SQL standard and works across PostgreSQL, MySQL, and Oracle—learn both syntaxes to write portable code
Using date extraction functions in WHERE clauses prevents index usage; restructure queries to use date range comparisons for dramatically better performance
Week numbering varies significantly between databases and regional standards (ISO vs. US), causing subtle bugs in reporting queries if not handled explicitly

Why Extract Date Parts?

Date manipulation sits at the core of nearly every reporting system. You need to group sales by quarter, filter orders placed on weekends, or calculate how many years someone has been a customer. Rather than storing redundant columns for year, month, and day, you extract these components from a single timestamp column when needed.

The challenge? Every database implements this differently. SQL Server uses DATEPART(), while PostgreSQL, MySQL, and Oracle follow the SQL standard with EXTRACT(). Understanding both approaches lets you write queries that work across your stack.

Syntax Comparison Across Databases

Here’s how the major databases handle date part extraction:

Database	Function	Syntax
SQL Server	DATEPART()	`DATEPART(part, date)`
PostgreSQL	EXTRACT()	`EXTRACT(part FROM date)`
MySQL	EXTRACT()	`EXTRACT(part FROM date)`
Oracle	EXTRACT()	`EXTRACT(part FROM date)`

The syntax difference is small but matters. SQL Server uses comma-separated arguments while the standard uses FROM keyword syntax.

-- SQL Server: Get the month from an order date
SELECT DATEPART(MONTH, order_date) AS order_month
FROM orders;

-- PostgreSQL / MySQL / Oracle: Same query using EXTRACT
SELECT EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

MySQL also supports DATEPART()-style functions like MONTH(), YEAR(), and DAY() as shortcuts:

-- MySQL shorthand functions
SELECT 
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day
FROM orders;

SQL Server offers these same shorthand functions, making simple extractions more readable. Use them when you only need one component.

Available Date Parts

The date parts you can extract vary slightly by database, but the core set remains consistent:

Part	SQL Server	PostgreSQL/MySQL	Description
Year	YEAR	YEAR	4-digit year
Quarter	QUARTER	QUARTER	1-4
Month	MONTH	MONTH	1-12
Day	DAY	DAY	1-31
Week	WEEK	WEEK	Week of year (varies by standard)
Day of Year	DAYOFYEAR	DOY	1-366
Weekday	WEEKDAY	DOW	Day of week (numbering varies)
Hour	HOUR	HOUR	0-23
Minute	MINUTE	MINUTE	0-59
Second	SECOND	SECOND	0-59

SQL Server includes additional parts like ISO_WEEK for ISO 8601 week numbering and MICROSECOND for sub-second precision. PostgreSQL’s EXTRACT() can pull EPOCH (Unix timestamp), TIMEZONE, and fractional seconds.

-- SQL Server: Extract multiple parts from a timestamp
SELECT 
    order_timestamp,
    DATEPART(YEAR, order_timestamp) AS year,
    DATEPART(QUARTER, order_timestamp) AS quarter,
    DATEPART(MONTH, order_timestamp) AS month,
    DATEPART(DAY, order_timestamp) AS day,
    DATEPART(HOUR, order_timestamp) AS hour,
    DATEPART(MINUTE, order_timestamp) AS minute,
    DATEPART(WEEKDAY, order_timestamp) AS weekday
FROM orders
WHERE order_id = 12345;

-- PostgreSQL: Same extraction
SELECT 
    order_timestamp,
    EXTRACT(YEAR FROM order_timestamp) AS year,
    EXTRACT(QUARTER FROM order_timestamp) AS quarter,
    EXTRACT(MONTH FROM order_timestamp) AS month,
    EXTRACT(DAY FROM order_timestamp) AS day,
    EXTRACT(HOUR FROM order_timestamp) AS hour,
    EXTRACT(MINUTE FROM order_timestamp) AS minute,
    EXTRACT(DOW FROM order_timestamp) AS weekday
FROM orders
WHERE order_id = 12345;

Practical Query Examples

Grouping Sales by Month and Year

The most common use case: aggregate data by time periods for reporting dashboards.

-- SQL Server: Monthly revenue report
SELECT 
    DATEPART(YEAR, order_date) AS year,
    DATEPART(MONTH, order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 
    DATEPART(YEAR, order_date),
    DATEPART(MONTH, order_date)
ORDER BY year, month;

-- PostgreSQL: Same report
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Filtering by Weekday

Find all orders placed on weekends for a promotion analysis:

-- SQL Server: Weekend orders (assuming Sunday=1, Saturday=7 with default DATEFIRST)
SELECT *
FROM orders
WHERE DATEPART(WEEKDAY, order_date) IN (1, 7);

-- PostgreSQL: Weekend orders (Sunday=0, Saturday=6)
SELECT *
FROM orders
WHERE EXTRACT(DOW FROM order_date) IN (0, 6);

Calculating Customer Tenure

Determine how many complete years a customer has been active:

-- SQL Server: Customer tenure in years
SELECT 
    customer_id,
    signup_date,
    DATEPART(YEAR, GETDATE()) - DATEPART(YEAR, signup_date) AS years_as_customer
FROM customers;

-- PostgreSQL: More accurate using AGE function, but EXTRACT works too
SELECT 
    customer_id,
    signup_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, signup_date)) AS years_as_customer
FROM customers;

Performance Considerations

Here’s where many developers get burned. Using DATEPART() or EXTRACT() in a WHERE clause wraps your column in a function, making the query non-sargable. The database cannot use an index on that column.

-- BAD: Non-sargable query - cannot use index on order_date
SELECT *
FROM orders
WHERE DATEPART(YEAR, order_date) = 2024
  AND DATEPART(MONTH, order_date) = 6;

-- GOOD: Sargable query - uses index on order_date
SELECT *
FROM orders
WHERE order_date >= '2024-06-01'
  AND order_date < '2024-07-01';

The difference in execution time can be orders of magnitude on large tables. I’ve seen queries drop from 30 seconds to 50 milliseconds simply by restructuring the WHERE clause.

For frequently filtered date components, consider computed columns with indexes:

-- SQL Server: Add a persisted computed column
ALTER TABLE orders
ADD order_year AS DATEPART(YEAR, order_date) PERSISTED;

CREATE INDEX IX_orders_year ON orders(order_year);

-- Now this query uses the index
SELECT * FROM orders WHERE order_year = 2024;

PostgreSQL achieves the same with expression indexes:

-- PostgreSQL: Expression index
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM order_date));

Common Pitfalls and Edge Cases

Week Numbering Chaos

Week-of-year calculations cause more bugs than any other date extraction. ISO 8601 defines week 1 as the week containing the first Thursday of the year. US standard often uses the week containing January 1. Some systems start weeks on Sunday, others on Monday.

-- SQL Server: Compare standard week vs ISO week
SELECT 
    order_date,
    DATEPART(WEEK, order_date) AS us_week,
    DATEPART(ISO_WEEK, order_date) AS iso_week
FROM orders
WHERE order_date BETWEEN '2024-12-28' AND '2025-01-05';

-- PostgreSQL: ISO week only
SELECT 
    order_date,
    EXTRACT(WEEK FROM order_date) AS iso_week
FROM orders
WHERE order_date BETWEEN '2024-12-28' AND '2025-01-05';

For December 30, 2024, you might get week 53 using one standard and week 1 using another. Document your choice explicitly in reporting code.

Weekday Numbering

SQL Server’s DATEPART(WEEKDAY, ...) returns values based on the @@DATEFIRST setting. The default makes Sunday = 1, but this varies by server configuration. PostgreSQL’s DOW always returns Sunday = 0.

-- SQL Server: Check and set first day of week
SELECT @@DATEFIRST;  -- 7 = Monday, 1 = Sunday (US default)

SET DATEFIRST 1;  -- Set Monday as first day of week

-- PostgreSQL: Consistent behavior, but different numbering
-- DOW: Sunday=0, Monday=1, ..., Saturday=6
-- ISODOW: Monday=1, ..., Sunday=7
SELECT 
    EXTRACT(DOW FROM order_date) AS dow,
    EXTRACT(ISODOW FROM order_date) AS isodow
FROM orders;

Timezone Handling

EXTRACT() operates on the timestamp as stored. If you store UTC and need to extract the hour in a user’s local timezone, convert first:

-- PostgreSQL: Extract hour in specific timezone
SELECT 
    EXTRACT(HOUR FROM created_at AT TIME ZONE 'America/New_York') AS local_hour
FROM events;

Quick Reference Cheat Sheet

Task	SQL Server	PostgreSQL/MySQL
Get year	`DATEPART(YEAR, col)` or `YEAR(col)`	`EXTRACT(YEAR FROM col)`
Get month	`DATEPART(MONTH, col)` or `MONTH(col)`	`EXTRACT(MONTH FROM col)`
Get day	`DATEPART(DAY, col)` or `DAY(col)`	`EXTRACT(DAY FROM col)`
Get quarter	`DATEPART(QUARTER, col)`	`EXTRACT(QUARTER FROM col)`
Get ISO week	`DATEPART(ISO_WEEK, col)`	`EXTRACT(WEEK FROM col)`
Get day of week	`DATEPART(WEEKDAY, col)`	`EXTRACT(DOW FROM col)`
Get hour	`DATEPART(HOUR, col)`	`EXTRACT(HOUR FROM col)`

Use dedicated functions (YEAR(), MONTH()) when available for readability. Reserve DATEPART()/EXTRACT() for less common parts like quarter or week. Always use date range comparisons in WHERE clauses instead of extracting parts for filtering. Your database will thank you with faster queries.