Loading…
Loading…
SQL tutorial · Intermediate
⏱️ 12 mins read
Date functions are critical in analytics — most business queries involve time periods, age calculations, or date formatting. Key functions: NOW()/CURRENT_DATE (current time), YEAR/MONTH/DAY (extract parts), DATE_DIFF (difference between dates), DATE_ADD/DATE_SUB (arithmetic), DATE_FORMAT/TO_CHAR (formatting).
NOW() / CURRENT_DATE()
YEAR(date) / MONTH(date) / DAY(date)
DATEDIFF(date1, date2)
DATE_ADD(date, INTERVAL n unit)
DATE_FORMAT(date, format)-- Current date/time
SELECT NOW(), CURRENT_DATE();
-- Extract date parts
SELECT
id,
YEAR(order_date) AS yr,
MONTH(order_date) AS mo,
DAYNAME(order_date) AS weekday
FROM orders;
-- Date arithmetic (one table per query — no mixing)
SELECT name, DATEDIFF(NOW(), hire_date) AS days_employed
FROM employees;
SELECT id, DATE_ADD(order_date, INTERVAL 30 DAY) AS return_deadline
FROM orders;
-- Group by month (MySQL allows the alias here; repeat the
-- expression in GROUP BY for portable SQL — see Topic 9)
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
SUM(total) AS revenue
FROM orders
GROUP BY month;| id | name | hire_date |
|---|---|---|
| 1 | Ada | 2021-03-01 |
| 2 | Grace | 2022-06-13 |
| 3 | Alan | 2020-11-02 |
| 4 | Edsger | 2023-08-19 |
| 5 | Barbara | 2024-01-08 |
| id | total | order_date | shipped_at |
|---|---|---|---|
| 1 | 240.00 | 2024-01-15 | 2024-01-18 |
| 2 | 89.90 | 2024-02-03 | 2024-02-07 |
| 3 | 430.00 | 2024-03-11 | NULL |
| 4 | 59.50 | 2024-04-02 | 2024-04-05 |
| 5 | 120.00 | 2024-05-20 | NULL |
You have seen this twice already: YEAR(hire_date) in Topic 3, LOWER(email) in Topic 12. Dates are where it bites most, because 'filter by year' is such a natural business question. Both queries below return the same five orders — but only one of them can use an index on order_date.
-- Not sargable: YEAR() evaluated for every row
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- Sargable: same meaning, seekable half-open range
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';DATEDIFF(shipped_at, order_date) computes delivery speed — except for pending orders, where shipped_at is NULL and the whole expression comes back NULL. AVG then silently divides by three, not five. Your average shipping time is measured only over orders that DID ship — which may be exactly the insight you need, or exactly the bias you must avoid.
SELECT
id,
DATEDIFF(shipped_at, order_date) AS ship_days,
ROUND(AVG(DATEDIFF(shipped_at, order_date)), 1) OVER () AS avg_ship
FROM orders;
-- 1 → 3, 2 → 4, 3 → NULL, 4 → 3, 5 → NULL
-- average = 10/3 ≈ 3.3 — computed over shipped orders onlyMonthly revenue is a one-liner in every engine and a different one-liner in each. This is where intermediate SQL starts feeling less like a language and more like dialects with a common ancestor — and why the roadmap repeats 'know your engine' at every level.
-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m') AS mo, SUM(total) AS rev
FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m');
-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM') AS mo, SUM(total) AS rev
FROM orders GROUP BY 1;
-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM') AS mo, SUM(total) AS rev
FROM orders GROUP BY FORMAT(order_date, 'yyyy-MM');Applying functions to indexed date columns in WHERE (e.g., YEAR(order_date) = 2024) prevents index usage. Use range conditions instead: WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'.
Know how to group by week, month, quarter. Know how to calculate age (DATEDIFF in years). PostgreSQL uses EXTRACT and DATE_TRUNC. SQL Server uses DATEPART and DATEDIFF with different argument order.
1. Why is WHERE YEAR(order_date) = 2024 the slow version of a year filter?
2. Why does BETWEEN '2024-01-01' AND '2024-12-31' lose rows on timestamp columns?
3. DATEDIFF(shipped_at, order_date) on ShopCo's orders — what comes back?
4. How do MySQL and SQL Server DATEDIFF differ?
5. Monthly revenue via DATE_FORMAT vs TO_CHAR vs FORMAT is an example of…?
6. What is the sargable rewrite of WHERE YEAR(shipped_at) = 2024?
Return each month (as 'YYYY-MM') with its net flow — the sum of all amounts that month — ordered chronologically.
⚡ Solve it in the SQL playground →When the column has a time component, '2024-12-31' means midnight at the START of Dec 31 — everything after 00:00:00 that day falls outside. The robust pattern is a half-open range: >= '2024-01-01' AND < '2025-01-01'.
Different argument conventions: MySQL's DATEDIFF(d1, d2) returns d1 minus d2 in days; SQL Server's DATEDIFF(unit, d1, d2) takes a date part first and counts boundaries crossed, not elapsed time (Dec 31 → Jan 1 is '1 year'). Always check the docs — this one silently produces wrong numbers.