SQL Date Functions: YEAR, MONTH, DATEDIFF & Ranges

Nearly every real table has a date column, and nearly every real question has a time dimension: how many orders this month, who was hired before 2020, how long has each employee been here. Date functions are how SQL answers those. This guide covers the small set that appears in almost every query, with the dialect differences flagged where they bite.

Pulling a date apart: YEAR, MONTH, DAY

SELECT order_id, order_date,
       YEAR(order_date) AS yr, MONTH(order_date) AS mo, DAY(order_date) AS dy
FROM Orders
LIMIT 4;

These three extract one component of a date as a plain number. That number then behaves like any other value — you can filter on it, sort by it, and, most usefully, group by it. PostgreSQL spells the same idea EXTRACT(YEAR FROM order_date) and SQLite uses strftime, but the concept is identical everywhere.

The monthly report: grouping by a date part

This is the single most common date-function pattern in working SQL — turn each date into its month, then aggregate per month:

SELECT MONTH(order_date) AS order_month, COUNT(*) AS orders, SUM(order_total) AS revenue
FROM Orders
GROUP BY MONTH(order_date)
ORDER BY order_month;

One honest caveat: months with no orders simply do not appear, because GROUP BY can only summarise rows that exist. Reports that must show empty months as zero rows need a calendar table or a generated series to join against — a technique worth knowing exists, even before you need it.

Filtering by date range — the safe pattern

SELECT order_id, order_date, status
FROM Orders
WHERE order_date >= '2024-03-01' AND order_date < '2024-04-01';

Note the shape: inclusive start, exclusive end. Asking for >= March 1 and < April 1 captures all of March no matter how the column stores time. The tempting alternative, BETWEEN '2024-03-01' AND '2024-03-31', quietly drops rows stamped 2024-03-31 14:30 the day the column gains a time component — because '2024-03-31' means midnight, and 14:30 is after midnight. The half-open range never has this problem, which is why it is the professional habit.

Plain comparisons work on dates too, exactly as they do on numbers:

SELECT first_name, hire_date
FROM Employees
WHERE hire_date < '2020-01-01'
ORDER BY hire_date;

The gap between two dates: DATEDIFF

SELECT first_name, hire_date, DATEDIFF(day, hire_date, '2026-08-29') AS days_employed
FROM Employees
ORDER BY days_employed DESC
LIMIT 5;

DATEDIFF answers "how much time between these two dates", here in days. This is the SQL Server argument order (unit first); MySQL's DATEDIFF takes just two dates and always returns days, and PostgreSQL subtracts dates directly with -. The unit-first form is what this playground's engine understands.

Cohorts: grouping by year

SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS hires
FROM Employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

The same monthly-report pattern at a coarser grain — one row per hiring year. Swap in any date column and any aggregate and this shape produces most of the "X per year" tables you have ever seen in a slide deck.

Two habits that prevent date bugs

Store dates as dates, write them as ISO

The format '2024-03-01' (year-month-day) is the ISO 8601 standard, every database accepts it, and it sorts correctly even when compared as text. Regional formats like 03/01/2024 mean different dates in different countries and have no place inside a query.

Prefer half-open ranges over BETWEEN

>= start AND < next-start keeps working when a date column gains a time component, when months have 28, 30 or 31 days, and when ranges need to chain without overlap. BETWEEN breaks quietly on all three.

Frequently asked questions

Related reading

• GROUP BY vs HAVING in SQL: What's the Difference?
• SQL String Functions: UPPER, SUBSTRING, TRIM & More
• The SQL WHERE Clause: Filtering Data with Every Operator