YEAR, MONTH, DAY extraction in SQL

Introduction

We use YEAR, MONTH, and DAY extraction to get parts of a date separately. This helps us understand or organize data by year, month, or day.

You want to find all records from a specific year, like sales in 2023.

You need to group data by month to see monthly trends, like monthly expenses.

You want to filter data for a specific day, like events happening on the 15th.

You want to display just the year or month from a full date in reports.

You want to compare parts of dates, like checking if two dates are in the same month.

Syntax

SQL

SELECT
  EXTRACT(YEAR FROM date_column) AS year_part,
  EXTRACT(MONTH FROM date_column) AS month_part,
  EXTRACT(DAY FROM date_column) AS day_part
FROM table_name;

EXTRACT function pulls out the year, month, or day from a date or timestamp.

The result is a number representing the part of the date you asked for.

Examples

Extracts the year 2024 from the date.

SQL

SELECT EXTRACT(YEAR FROM DATE '2024-06-15') AS year;

Extracts the month 6 (June) from the date.

SQL

SELECT EXTRACT(MONTH FROM DATE '2024-06-15') AS month;

Extracts the day 15 from the date.

SQL

SELECT EXTRACT(DAY FROM DATE '2024-06-15') AS day;

Extracts year and month from the order_date column in the orders table.

SQL

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Sample Program

This creates a table with event dates, inserts three events, and extracts year, month, and day from each event_date.

SQL

CREATE TABLE events (
  id INT,
  event_date DATE
);

INSERT INTO events (id, event_date) VALUES
(1, DATE '2024-06-15'),
(2, DATE '2023-12-25'),
(3, DATE '2024-01-01');

SELECT id,
       EXTRACT(YEAR FROM event_date) AS year,
       EXTRACT(MONTH FROM event_date) AS month,
       EXTRACT(DAY FROM event_date) AS day
FROM events
ORDER BY id;

Important Notes

Some SQL databases also support functions like YEAR(), MONTH(), DAY() as shortcuts.

Make sure your date column is in a date or timestamp format for EXTRACT to work correctly.

EXTRACT returns numbers, so you can use them in calculations or comparisons.

Summary

Use EXTRACT to get year, month, or day from a date.

This helps filter, group, or display parts of dates easily.

Works with date or timestamp columns in your database.