Topic 35 of 52 · Intermediate
Functions Reference: Date, NULL & Aggregate
Date/time, NULL-handling, conditional, aggregate and window functions in one place.
Concept
Date functions get the current time, pull out parts of a date, add intervals and find differences — needed in almost every report.
NULL and control-flow functions (COALESCE, IFNULL, NULLIF, IF, CASE) replace missing values and add if/else logic.
Aggregate functions summarise many rows into one; window functions compute over related rows while keeping each row.
Cheat sheet
| Function | What it does | Example → result |
|---|---|---|
| NOW() / CURRENT_TIMESTAMP | Current date and time | NOW() → '2026-10-30 16:40:00' |
| CURDATE() / CURTIME() | Current date / time | CURDATE() → '2026-10-30' |
| DATE(dt) | Date part of a datetime | DATE('2026-10-30 10:00') → '2026-10-30' |
| YEAR / MONTH / DAY(d) | Extract a part | MONTH('2026-10-30') → 10 |
| DAYNAME(d) / MONTHNAME(d) | Name of day / month | DAYNAME('2026-10-30') → 'Friday' |
| DAYOFWEEK(d) / WEEK(d) | Day number (1=Sun) / week number | DAYOFWEEK('2026-10-30') → 6 |
| DATE_ADD(d, INTERVAL n unit) | Add time | DATE_ADD('2026-01-31', INTERVAL 1 MONTH) → '2026-02-28' |
| DATE_SUB(d, INTERVAL n unit) | Subtract time | DATE_SUB(CURDATE(), INTERVAL 7 DAY) |
| DATEDIFF(a, b) | Days between dates | DATEDIFF('2026-10-30','2026-10-01') → 29 |
| TIMESTAMPDIFF(unit, a, b) | Difference in any unit | TIMESTAMPDIFF(YEAR,'2000-05-01',CURDATE()) → 26 |
| DATE_FORMAT(d, fmt) | Format a date as text | DATE_FORMAT(d,'%Y-%m') → '2026-10' |
| STR_TO_DATE(s, fmt) | Parse text into a date | STR_TO_DATE('30/10/2026','%d/%m/%Y') |
| LAST_DAY(d) | Last day of that month | LAST_DAY('2026-02-10') → '2026-02-28' |
| TIMESTAMPADD(unit, n, d) | Add n units to a datetime | TIMESTAMPADD(HOUR, 5, '2026-10-30 10:00') → '2026-10-30 15:00' |
| EXTRACT(unit FROM d) | Pull one part (standard SQL) | EXTRACT(YEAR FROM '2026-10-30') → 2026 |
| MAKEDATE(year, day_of_year) | Build a date from year + day number | MAKEDATE(2026, 32) → '2026-02-01' |
| COALESCE(a, b, ...) | First non-NULL value | COALESCE(NULL,'x') → 'x' |
| IFNULL(a, b) | b if a is NULL | IFNULL(phone,'-') |
| NULLIF(a, b) | NULL if a = b (avoid divide by zero) | x / NULLIF(y, 0) |
| IF(cond, a, b) | Inline if/else | IF(amount>1000,'big','small') |
| CASE WHEN ... END | Multi-branch if/else | CASE WHEN m>=90 THEN 'A' ELSE 'B' END |
| COUNT / SUM / AVG | Count, total, average | SUM(amount) |
| MIN / MAX | Smallest / largest | MAX(order_date) |
| GROUP_CONCAT(col) | Join group values into one string | GROUP_CONCAT(name ORDER BY name) |
| STDDEV / VARIANCE | Spread of values | STDDEV(salary) |
| ROW_NUMBER() OVER(...) | 1, 2, 3 per partition | Latest row per customer |
| RANK() / DENSE_RANK() | Rank with / without gaps | Top-N per group |
| NTILE(n) | Split into n buckets | NTILE(4) → quartiles |
| LAG(col) / LEAD(col) | Previous / next row value | Month-over-month change |
| FIRST_VALUE / LAST_VALUE | First / last value in window | First purchase amount |
| SUM(col) OVER(ORDER BY ...) | Running total | Cumulative revenue |
Code (SQL · MySQL 8)
SELECT
customer_id,
DATE_FORMAT(order_date, '%b %Y') AS month_label,
DATEDIFF(CURDATE(), order_date) AS days_ago,
COALESCE(coupon_code, 'NONE') AS coupon,
IF(status = 'delivered', amount, 0) AS delivered_amount,
ROUND(amount / NULLIF(qty, 0), 2) AS unit_price,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);
SELECT city, GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ') AS customers
FROM customers GROUP BY city;Remember
- Use NULLIF(y, 0) to avoid divide-by-zero errors.
- Filter date ranges with >= and < instead of YEAR(col) = ... to keep indexes usable.
- Aggregates collapse rows; window functions keep them.
Interview questions
How do you calculate someone's age in years?
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) — it handles birthdays that haven't happened yet this year.
COALESCE vs IFNULL?
IFNULL takes exactly two arguments and is MySQL-specific; COALESCE takes any number and is standard SQL.
Lesson test
Take an 8-question test on “Functions Reference: Date, NULL & Aggregate” — concepts, output prediction, bug spotting and interview questions.