YEAR MONTH DAY

YEAR, MONTH and DAY are like tearing a date into pieces and keeping only the year, or the month, or the day.

In this page:

The YEAR Function
The MONTH Function
The DAY Function
Day of the Week and Year
Filtering Table Records

Syntax

sql

YEAR(date)
MONTH(date)
DAY(date)

The YEAR Function

YEAR pulls the four-digit year out of a date value, which is the simplest way to group or filter records by year, such as totaling sales for a specific calendar year.

Example: The YEAR Function

sql

CREATE TABLE orders (id INT, order_date DATE, total INT);
INSERT INTO orders VALUES (1, '2023-05-01', 50), (2, '2024-01-15', 70);
SELECT YEAR(order_date) AS order_year, SUM(total) AS yearly_total FROM orders GROUP BY YEAR(order_date);

The MONTH Function

MONTH extracts just the numeric month from a date, while the related MONTHNAME function returns the month spelled out as text, giving you a choice between the raw number and a human-readable label.

Example: The MONTH Function

sql

SELECT MONTH('2024-06-15') AS month_number, MONTHNAME('2024-06-15') AS month_text;

The DAY Function

DAY returns the day-of-month number from a date, and DAYOFMONTH is a synonym that returns the exact same result, so either name works depending on which reads more clearly in your query.

Example: The DAY Function

sql

SELECT DAY('2024-06-15') AS day_num, DAYOFMONTH('2024-06-15') AS day_num_alias;

Day of the Week and Year

Beyond the calendar day, MySQL can also tell you which day of the week or which numbered day of the year a date falls on, returning that as a plain integer you can use for scheduling logic.

Example: Day of the Week and Year

sql

SELECT DAYOFWEEK('2024-06-15') AS weekday_num, DAYOFYEAR('2024-06-15') AS year_day_num;

Filtering Table Records

These extraction functions are especially useful inside a WHERE clause, letting you filter a table down to just the rows from a particular year or month without needing to match an exact date range.

Example: Filtering Table Records

sql

CREATE TABLE orders (id INT, order_date DATE);
INSERT INTO orders VALUES (1, '2024-03-10'), (2, '2023-03-10');
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Chapter Quiz — Complete all 6 topics to unlock

0/6 topics done

Complete these topics first:

← Previous Lesson DATEDIFF & DATE_ADD Next Lesson → CREATE VIEW