SQL Date Functions: YEAR, MONTH, DATEDIFF & Ranges

SQL date functions let a query take a date apart into its year, month or day, measure the gap between two dates, shift a date forward or back, and ask for today’s date. 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. This guide covers the small set that appears in almost every query, with the dialect differences flagged where they bite, because date functions are where databases disagree most.

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
ORDER BY order_id
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. YEAR, MONTH and DAY are the MySQL and SQL Server names; here they are the playground’s helpers. SQLite’s own tool is strftime, which formats a date with a pattern and returns text:

SELECT order_id, order_date,
       strftime('%Y', order_date) AS yr_text,
       CAST(strftime('%m', order_date) AS INTEGER) AS mo
FROM Orders
ORDER BY order_id
LIMIT 4;

strftime('%Y', ...) gives the text '2024', not the number 2024, so wrap it in CAST(... AS INTEGER) whenever you compare it with a number or do arithmetic on it. The concept is the same everywhere; only the spelling moves:

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.

A second caveat: every sample order falls in 2024, so grouping by the month alone is safe here. On data that spans years, group by year and month — GROUP BY YEAR(order_date), MONTH(order_date), or the month-start date shown further down — or January 2024 and January 2025 land in the same row.

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. In SQLite, where a date is ISO text, that works because year-month-day text sorts in date order:

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

Today’s date, and the last 30 days

Every database can tell you the current date, and each spells it differently. In SQLite:

SELECT date('now') AS today, CURRENT_DATE AS also_today;

The answer changes every day, which is why this page does not print it. For the time as well, use datetime('now') or CURRENT_TIMESTAMP. SQLite reports 'now' in UTC, so for part of the day, depending on your time zone, its “today” is not the date on your calendar; date('now', 'localtime') gives the local date.

The everyday use is a rolling window, such as orders from the last 30 days: WHERE order_date >= date('now', '-30 days'). On the sample data that finds nothing, because every order is from 2024, so the runnable version anchors the window to the latest order instead of to today:

SELECT order_id, order_date, order_total
FROM Orders
WHERE order_date >= date((SELECT MAX(order_date) FROM Orders), '-30 days')
ORDER BY order_date;

The latest order is dated 29 May 2024, so the window opens on 29 April and three orders fall inside it. The same filter against today is order_date >= CURRENT_DATE - INTERVAL '30 days' in PostgreSQL, order_date >= CURDATE() - INTERVAL 30 DAY in MySQL and order_date >= DATEADD(day, -30, CAST(GETDATE() AS date)) in SQL Server.

Shifting a date: adding days and month starts

SQLite moves a date by passing date() a modifier: a signed number and a unit.

SELECT order_id, order_date,
       date(order_date, '+7 days') AS follow_up,
       date(order_date, '-1 day') AS day_before
FROM Orders
ORDER BY order_id
LIMIT 4;

Other units work the same way ('+1 month', '-1 year'), but month arithmetic is where databases disagree. Ask SQLite for 31 January plus one month and it rolls past the short month:

SELECT date('2024-01-31', '+1 month') AS one_month_later;

MySQL, PostgreSQL and SQL Server stop at the last day of February (2024-02-29) instead. Neither rule is wrong, but they are different, so test month arithmetic on the 29th, 30th and 31st before trusting it.

The first day of the month

The other everyday shift is back to the first of the month. It turns every date into a month label that sorts correctly and never merges two years:

SELECT strftime('%Y-%m-01', order_date) AS month_start, COUNT(*) AS orders
FROM Orders
GROUP BY strftime('%Y-%m-01', order_date)
ORDER BY month_start;

strftime('%Y-%m-01', order_date) keeps the year and month and pins the day to 01; date(order_date, 'start of month') does the same. Elsewhere it is DATE_TRUNC('month', order_date) in PostgreSQL, DATE_FORMAT(order_date, '%Y-%m-01') in MySQL, and DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) in SQL Server, which also offers DATETRUNC(month, order_date) from SQL Server 2022.

The gap between two dates: DATEDIFF and julianday

How many days has each employee been here? SQLite has no DATEDIFF. It turns each date into a day number with julianday(), and the difference between two day numbers is the gap in days:

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

julianday() counts days from a fixed starting point in 4714 BC and returns a real number, because it can carry a time of day as a fraction. For plain dates the difference is a whole number of days; if the columns hold times as well, wrap it in ROUND() or CAST(... AS INTEGER). MySQL and SQL Server call this DATEDIFF, and they put its arguments in opposite orders:

-- SQL Server: DATEDIFF(unit, start, end)
SELECT TOP 5 first_name, hire_date,
       DATEDIFF(day, hire_date, '2026-08-29') AS days_employed
FROM Employees
ORDER BY days_employed DESC;

-- MySQL: DATEDIFF(end, start), always in days
SELECT first_name, hire_date,
       DATEDIFF('2026-08-29', hire_date) AS days_employed
FROM Employees
ORDER BY days_employed DESC
LIMIT 5;

Same name, opposite order: MySQL subtracts its second argument from its first, SQL Server subtracts its second from its third. PostgreSQL has no DATEDIFF and subtracts one date from the other directly (DATE '2026-08-29' - hire_date). If a “days since” report comes out negative after a move between databases, check the argument order first.

Earliest and latest: MIN and MAX

MIN and MAX work on dates as well as numbers: the earliest date is the smallest and the latest is the largest. Per customer, that gives a first-and-latest-order report in one query:

SELECT c.company, MIN(o.order_date) AS first_order, MAX(o.order_date) AS latest_order
FROM Customers c
JOIN Orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.company
ORDER BY latest_order DESC, c.company;

Cedar Health ordered most recently, on 29 May 2024. Baltic Systems and Kilimanjaro Foods ordered only once, so their first and latest orders are the same day. In SQLite these dates are text, so MIN and MAX are comparing text here; that gives the right answer only because year-month-day text sorts in date order, which is one more reason to keep dates in that format.

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

Use a real DATE or TIMESTAMP column where the database has one. SQLite has no date type at all, which is why the sample database keeps its dates as text in one fixed format. Either way, write dates as '2024-03-01' (year-month-day): it 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.

Practice this topic

Reading explains it; solving it is what makes it stick. This exercise uses the monthly-report pattern from this page, and your answer is checked by running it against the same database:

• Orders by month Medium

See all 42 exercises →

Frequently asked questions

Related reading

• SQL ORDER BY and LIMIT: Sorting, Top-N and Pagination
• 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
• PostgreSQL vs MySQL vs SQL Server vs SQLite: SQL Syntax Differences