EXTRACT and YEAR/MONTH/DAY in MySQL

Introduction

You use EXTRACT to get just the year, month, or day from a date. It helps you focus on parts of a date without extra details.

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

When you need to group data by month to see monthly trends.

When filtering events that happened on a certain day of the month.

When you want to compare just the year part of dates in two columns.

When creating reports that show data by year, month, or day.

Syntax

MySQL

EXTRACT(unit FROM date)

-- unit can be YEAR, MONTH, DAY, etc.
-- date is a date or datetime column or value

The unit tells EXTRACT which part of the date you want.

The date can be a column name or a date value like '2024-06-15'.

Examples

Gets the year part, which is 2024.

MySQL

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

Gets the month part, which is 6 (June).

MySQL

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

Gets the day part, which is 15.

MySQL

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

Gets the year from the order_date column in the orders table.

MySQL

SELECT EXTRACT(YEAR FROM order_date) FROM orders;

Sample Program

This creates a small table of events with dates. Then it selects each event's id and date, plus the year, month, and day parts extracted separately.

MySQL

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

SELECT id, event_date, EXTRACT(YEAR FROM event_date) AS year_part, EXTRACT(MONTH FROM event_date) AS month_part, EXTRACT(DAY FROM event_date) AS day_part FROM events ORDER BY id;

Important Notes

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

If the date is NULL or invalid, EXTRACT returns NULL.

You can use EXTRACT with other units like HOUR, MINUTE, SECOND for datetime values.

Summary

EXTRACT helps get year, month, or day from a date easily.

Use it to filter, group, or compare parts of dates.

It works with date or datetime values and returns numbers.