YEAR MONTH DAY
In this page:
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
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
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
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
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
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: