← SQL roadmap

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

FunctionWhat it doesExample → result
NOW() / CURRENT_TIMESTAMPCurrent date and timeNOW() → '2026-10-30 16:40:00'
CURDATE() / CURTIME()Current date / timeCURDATE() → '2026-10-30'
DATE(dt)Date part of a datetimeDATE('2026-10-30 10:00') → '2026-10-30'
YEAR / MONTH / DAY(d)Extract a partMONTH('2026-10-30') → 10
DAYNAME(d) / MONTHNAME(d)Name of day / monthDAYNAME('2026-10-30') → 'Friday'
DAYOFWEEK(d) / WEEK(d)Day number (1=Sun) / week numberDAYOFWEEK('2026-10-30') → 6
DATE_ADD(d, INTERVAL n unit)Add timeDATE_ADD('2026-01-31', INTERVAL 1 MONTH) → '2026-02-28'
DATE_SUB(d, INTERVAL n unit)Subtract timeDATE_SUB(CURDATE(), INTERVAL 7 DAY)
DATEDIFF(a, b)Days between datesDATEDIFF('2026-10-30','2026-10-01') → 29
TIMESTAMPDIFF(unit, a, b)Difference in any unitTIMESTAMPDIFF(YEAR,'2000-05-01',CURDATE()) → 26
DATE_FORMAT(d, fmt)Format a date as textDATE_FORMAT(d,'%Y-%m') → '2026-10'
STR_TO_DATE(s, fmt)Parse text into a dateSTR_TO_DATE('30/10/2026','%d/%m/%Y')
LAST_DAY(d)Last day of that monthLAST_DAY('2026-02-10') → '2026-02-28'
TIMESTAMPADD(unit, n, d)Add n units to a datetimeTIMESTAMPADD(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 numberMAKEDATE(2026, 32) → '2026-02-01'
COALESCE(a, b, ...)First non-NULL valueCOALESCE(NULL,'x') → 'x'
IFNULL(a, b)b if a is NULLIFNULL(phone,'-')
NULLIF(a, b)NULL if a = b (avoid divide by zero)x / NULLIF(y, 0)
IF(cond, a, b)Inline if/elseIF(amount>1000,'big','small')
CASE WHEN ... ENDMulti-branch if/elseCASE WHEN m>=90 THEN 'A' ELSE 'B' END
COUNT / SUM / AVGCount, total, averageSUM(amount)
MIN / MAXSmallest / largestMAX(order_date)
GROUP_CONCAT(col)Join group values into one stringGROUP_CONCAT(name ORDER BY name)
STDDEV / VARIANCESpread of valuesSTDDEV(salary)
ROW_NUMBER() OVER(...)1, 2, 3 per partitionLatest row per customer
RANK() / DENSE_RANK()Rank with / without gapsTop-N per group
NTILE(n)Split into n bucketsNTILE(4) → quartiles
LAG(col) / LEAD(col)Previous / next row valueMonth-over-month change
FIRST_VALUE / LAST_VALUEFirst / last value in windowFirst purchase amount
SUM(col) OVER(ORDER BY ...)Running totalCumulative 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.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Functions Reference: Date, NULL & Aggregate” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.