Date Functions

Intermediate

⏱️ 12 mins read

Works with dates across employees (Topic 1: hire_date) and orders (Topic 7: order_date, shipped_at) — shipped_at is NULL for pending orders, which is where dates meet NULLs.

What You'll Learn

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).

Syntax

NOW() / CURRENT_DATE()
YEAR(date) / MONTH(date) / DAY(date)
DATEDIFF(date1, date2)
DATE_ADD(date, INTERVAL n unit)
DATE_FORMAT(date, format)

Example

-- 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;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- 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

-- orders (from Topic 7)
-- 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

Functions on dates kill indexes — Topic 3's rule, date edition

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';
Half-open ranges (>= start AND < next_start) beat BETWEEN on timestamps too — BETWEEN '...12-31' misses everything after midnight on the last day.

Date math with NULL: shipping time only exists for shipped orders

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 only
Every 'average time-to-X' metric inherits a survivorship bias when unfinished cases are NULL. State the denominator out loud (Topic 8).

One calculation, three dialects — plan for the portability tax

Monthly 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');
The concepts (truncate to month, then aggregate) are universal; only the function names rotate. Learn the concepts once, keep a dialect cheat-sheet handy.

Common Mistakes

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'.

Interview Tips

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.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

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?

Practice

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 →

Frequently Asked Questions

Why does BETWEEN '2024-01-01' AND '2024-12-31' miss rows?

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'.

Why is DATEDIFF so inconsistent between databases?

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.

Back to Full Roadmap