✏️ Explanatory Question
Level: Intermediate — Vital for reporting, scheduling, and any time-based filtering or calculations.
Date and time functions let you retrieve the current date/time, extract parts of a date, perform date arithmetic, and format dates for display.
DATE_ADD()/DATE_SUB() with the INTERVAL keyword (e.g., INTERVAL 7 DAY) for date math — never manually add numbers, as that ignores calendar rules like month lengths and leap years.
| Function | Example | Result (sample) |
|---|---|---|
| NOW() | NOW() |
2026-08-02 13:05:00 |
| CURDATE() | CURDATE() |
2026-08-02 |
| YEAR() | YEAR('2026-08-02') |
2026 |
| DATE_ADD() | DATE_ADD('2026-08-02', INTERVAL 7 DAY) |
2026-08-09 |
| DATEDIFF() | DATEDIFF('2026-08-10','2026-08-02') |
8 |
| DATE_FORMAT() | DATE_FORMAT(NOW(), '%d-%m-%Y') |
02-08-2026 |
-- Current date and time
SELECT NOW(), CURDATE(), CURTIME();
-- Orders placed in the last 30 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
-- Extract year and month for grouping
SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mth,
COUNT(*) AS total
FROM orders
GROUP BY yr, mth;
-- Calculate employee age in years
SELECT name,
TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM employees;
-- Format a date for display
SELECT DATE_FORMAT(NOW(), '%W, %d %M %Y') AS pretty_date;
-- e.g., 'Sunday, 02 August 2026'