SQL - YEAR(), MONTH(), DAY() Functions
Every non-trivial database application eventually needs to slice data by time. Monthly revenue reports, quarterly comparisons, year-over-year growth analysis—these all require breaking dates into...
Key Insights
- YEAR(), MONTH(), and DAY() functions extract integer components from date values, enabling flexible date-based filtering, grouping, and reporting in SQL queries.
- These functions work consistently in MySQL and SQL Server, but PostgreSQL requires EXTRACT() or DATE_PART()—knowing the differences prevents cross-database headaches.
- Using date functions in WHERE clauses can kill query performance by preventing index usage; prefer date range comparisons for filtered queries on large tables.
Why Date Component Extraction Matters
Every non-trivial database application eventually needs to slice data by time. Monthly revenue reports, quarterly comparisons, year-over-year growth analysis—these all require breaking dates into their constituent parts. The YEAR(), MONTH(), and DAY() functions are your workhorses for this task.
These functions take a date or datetime value and return the corresponding integer component. Simple concept, but the implementation details and performance implications deserve attention.
Function Syntax and Basic Usage
The syntax is straightforward across MySQL and SQL Server:
-- MySQL and SQL Server syntax
SELECT YEAR('2024-03-15'); -- Returns: 2024
SELECT MONTH('2024-03-15'); -- Returns: 3
SELECT DAY('2024-03-15'); -- Returns: 15
Each function accepts a date, datetime, or timestamp value and returns an integer. The DAY() function specifically returns the day of the month (1-31), not the day of the week or day of the year.
You can also work with the current date:
-- Get current date components
SELECT
YEAR(CURRENT_DATE) AS current_year,
MONTH(CURRENT_DATE) AS current_month,
DAY(CURRENT_DATE) AS current_day;
SQL Server additionally supports GETDATE() for the current datetime:
-- SQL Server specific
SELECT
YEAR(GETDATE()) AS current_year,
MONTH(GETDATE()) AS current_month,
DAY(GETDATE()) AS current_day;
Extracting Date Parts from Table Data
Real-world usage involves extracting components from table columns. Consider an orders table with an order_date column:
-- Extract date components from order records
SELECT
order_id,
customer_id,
order_date,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day,
total_amount
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date;
This query produces output like:
| order_id | customer_id | order_date | order_year | order_month | order_day | total_amount |
|---|---|---|---|---|---|---|
| 1001 | 42 | 2024-01-15 | 2024 | 1 | 15 | 299.99 |
| 1002 | 17 | 2024-02-03 | 2024 | 2 | 3 | 149.50 |
You can combine these extracted values for formatted output:
-- Create a formatted month-year string
SELECT
order_id,
CONCAT(YEAR(order_date), '-', LPAD(MONTH(order_date), 2, '0')) AS year_month,
total_amount
FROM orders;
Filtering Data by Date Components
Using these functions in WHERE clauses enables flexible date filtering without knowing exact dates:
-- Find all orders from March 2024
SELECT order_id, order_date, total_amount
FROM orders
WHERE YEAR(order_date) = 2024
AND MONTH(order_date) = 3;
-- Find orders from Q4 (October, November, December) of any year
SELECT order_id, order_date, total_amount
FROM orders
WHERE MONTH(order_date) IN (10, 11, 12);
-- Find orders placed on the 15th of any month
SELECT order_id, order_date, total_amount
FROM orders
WHERE DAY(order_date) = 15;
Combining conditions allows for complex temporal queries:
-- Find orders from Q1 2024 (January through March)
SELECT
order_id,
order_date,
total_amount
FROM orders
WHERE YEAR(order_date) = 2024
AND MONTH(order_date) BETWEEN 1 AND 3;
-- Find orders from the same month as today, any year
SELECT order_id, order_date, total_amount
FROM orders
WHERE MONTH(order_date) = MONTH(CURRENT_DATE);
Grouping and Aggregating by Date Parts
The most common use case for date extraction functions is grouping data for reports. Monthly and yearly aggregations are standard business requirements:
-- Monthly sales totals for 2024
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
COUNT(*) AS order_count,
SUM(total_amount) AS monthly_revenue,
AVG(total_amount) AS avg_order_value
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;
For year-over-year comparisons:
-- Compare monthly revenue across years
SELECT
MONTH(order_date) AS month,
SUM(CASE WHEN YEAR(order_date) = 2023 THEN total_amount ELSE 0 END) AS revenue_2023,
SUM(CASE WHEN YEAR(order_date) = 2024 THEN total_amount ELSE 0 END) AS revenue_2024,
SUM(CASE WHEN YEAR(order_date) = 2024 THEN total_amount ELSE 0 END) -
SUM(CASE WHEN YEAR(order_date) = 2023 THEN total_amount ELSE 0 END) AS yoy_change
FROM orders
WHERE YEAR(order_date) IN (2023, 2024)
GROUP BY MONTH(order_date)
ORDER BY month;
Daily aggregations work the same way:
-- Daily order counts for the current month
SELECT
DAY(order_date) AS day_of_month,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_revenue
FROM orders
WHERE YEAR(order_date) = YEAR(CURRENT_DATE)
AND MONTH(order_date) = MONTH(CURRENT_DATE)
GROUP BY DAY(order_date)
ORDER BY day_of_month;
Cross-Database Alternatives
PostgreSQL doesn’t support YEAR(), MONTH(), and DAY() functions directly. Instead, it uses EXTRACT() or DATE_PART():
-- PostgreSQL syntax using EXTRACT
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DAY FROM order_date) AS order_day
FROM orders;
-- PostgreSQL syntax using DATE_PART
SELECT
DATE_PART('year', order_date) AS order_year,
DATE_PART('month', order_date) AS order_month,
DATE_PART('day', order_date) AS order_day
FROM orders;
Here’s a side-by-side comparison:
-- MySQL / SQL Server
SELECT
YEAR(order_date) AS yr,
MONTH(order_date) AS mo,
DAY(order_date) AS dy
FROM orders;
-- PostgreSQL
SELECT
EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mo,
EXTRACT(DAY FROM order_date) AS dy
FROM orders;
-- Standard SQL (works in most databases)
SELECT
EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mo,
EXTRACT(DAY FROM order_date) AS dy
FROM orders;
Note that EXTRACT() is part of the SQL standard, so it works in MySQL and SQL Server too, making it the more portable choice for cross-database applications.
PostgreSQL’s EXTRACT() returns a double precision value, not an integer. Cast explicitly if you need integer behavior:
-- PostgreSQL: Cast to integer for consistent behavior
SELECT
EXTRACT(YEAR FROM order_date)::INTEGER AS order_year,
EXTRACT(MONTH FROM order_date)::INTEGER AS order_month
FROM orders;
Performance Considerations
Here’s where things get serious. Using functions on columns in WHERE clauses prevents the database from using indexes on those columns. This is called a non-sargable predicate, and it can devastate query performance on large tables.
Consider these two queries:
-- BAD: Non-sargable - cannot use index on order_date
SELECT order_id, total_amount
FROM orders
WHERE YEAR(order_date) = 2024
AND MONTH(order_date) = 3;
-- GOOD: Sargable - can use index on order_date
SELECT order_id, total_amount
FROM orders
WHERE order_date >= '2024-03-01'
AND order_date < '2024-04-01';
Both queries return the same results, but the second one can leverage an index on order_date. On a table with millions of rows, this difference can mean seconds versus milliseconds.
For the first query, the database must evaluate YEAR() and MONTH() for every single row before it can determine which rows match. For the second query, it can use a B-tree index to jump directly to the relevant rows.
The same principle applies to GROUP BY clauses, though the impact is less severe since you’re typically scanning more data anyway:
-- Consider creating a covering index for this pattern
CREATE INDEX idx_orders_date_amount ON orders(order_date, total_amount);
-- This query benefits from the covering index
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
SUM(total_amount) AS monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
GROUP BY YEAR(order_date), MONTH(order_date);
For frequently-run reports, consider materialized views or computed columns:
-- SQL Server: Add computed columns for common groupings
ALTER TABLE orders
ADD order_year AS YEAR(order_date) PERSISTED,
order_month AS MONTH(order_date) PERSISTED;
-- Now you can index these columns directly
CREATE INDEX idx_orders_year_month ON orders(order_year, order_month);
The YEAR(), MONTH(), and DAY() functions are essential tools for date manipulation in SQL. Use them freely in SELECT lists and GROUP BY clauses. But when filtering data in WHERE clauses on indexed columns, prefer date range comparisons to maintain query performance. Know your database’s syntax variations, and your date-based queries will be both correct and efficient.