CHAPTER 24
Working with dates - extraction, arithmetic, formatting.
| Function | Returns | Example |
|---|---|---|
| NOW() | Current date + time | 2024-03-29 14:30:00 |
| CURDATE() | Current date only | 2024-03-29 |
| YEAR(date) | Year integer | YEAR('2024-03-29') → 2024 |
| MONTH(date) | Month integer | MONTH('2024-03-29') → 3 |
| DAY(date) | Day integer | DAY('2024-03-29') → 29 |
| MONTHNAME(date) | Month name | MONTHNAME('2024-03-29') → March |
| DAYNAME(date) | Weekday name | DAYNAME('2024-03-29') → Friday |
| DATEDIFF(d1,d2) | Days between dates | DATEDIFF('2024-12-31','2024-01-01') → 365 |
| TIMESTAMPDIFF(unit,d1,d2) | Difference in any unit | TIMESTAMPDIFF(YEAR, dob, CURDATE()) |
| DATE_ADD(date, INTERVAL n) | Add to a date | DATE_ADD('2024-01-01', INTERVAL 30 DAY) |
| DATE_SUB(date, INTERVAL n) | Subtract from a date | DATE_SUB(CURDATE(), INTERVAL 90 DAY) |
Order from the last 90 days
SELECT order_id, order_date, total FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);