SQL EXTRACT / DATEPART: Get Year, Month, Day from Date (All Databases)

Overview

Extract specific parts (year, month, day, hour) from a date or timestamp. Essential for grouping, filtering, and reporting by time periods.

MySQL

-- EXTRACT:
SELECT EXTRACT(YEAR FROM '2026-03-12');    -- 2026
SELECT EXTRACT(MONTH FROM '2026-03-12');   -- 3
SELECT EXTRACT(DAY FROM '2026-03-12');     -- 12

-- Shorthand functions:
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at)
FROM orders;

-- Group by month:
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mo,
  COUNT(*) AS orders
FROM orders
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY yr, mo;

PostgreSQL

-- EXTRACT:
SELECT EXTRACT(YEAR FROM TIMESTAMP '2026-03-12 14:30:00');   -- 2026
SELECT EXTRACT(MONTH FROM CURRENT_DATE);   -- 3
SELECT EXTRACT(DOW FROM CURRENT_DATE);     -- day of week (0=Sun)
SELECT EXTRACT(EPOCH FROM NOW());           -- Unix timestamp

-- DATE_PART (alternative):
SELECT DATE_PART('year', created_at) FROM orders;

-- DATE_TRUNC for grouping:
SELECT DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

SQL Server

-- DATEPART:
SELECT DATEPART(YEAR, GETDATE());    -- 2026
SELECT DATEPART(MONTH, GETDATE());   -- 3
SELECT DATEPART(DAY, GETDATE());     -- 12
SELECT DATEPART(WEEKDAY, GETDATE()); -- day of week

-- Shorthand functions:
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at)
FROM orders;

-- DATENAME for text:
SELECT DATENAME(MONTH, GETDATE());  -- 'March'
SELECT DATENAME(WEEKDAY, GETDATE()); -- 'Thursday'

Oracle

-- EXTRACT:
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;   -- 2026
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;  -- 3
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;    -- 12

-- TO_CHAR for more parts:
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;  -- 'THU'
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL;   -- '1' (quarter)

Skip the Syntax Lookup

Instead of memorizing EXTRACT / DATEPART syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions