Date Operations
SQL date operations extract, add, subtract, and compare dates. They are common in analytics queries, time-series analysis, and reporting — particularly when paired with window functions for rolling calculations, where the date column drives ORDER BY inside the window frame.
DATE_ADD
DATE_ADD adds a date or time interval.
SELECT DATE_ADD('2025-12-12', INTERVAL 1 DAY);
SELECT DATE_ADD('2025-01-01', INTERVAL 3 MONTH);Syntax:
DATE_ADD(date, INTERVAL value unit)DATE_SUB
DATE_SUB subtracts a date or time interval.
SELECT DATE_SUB('2025-12-31', INTERVAL 7 DAY);INTERVAL
INTERVAL specifies a duration.
SELECT task_id, start_date, end_date
FROM tasks
WHERE (end_date - start_date) > INTERVAL 5 DAY;Common units include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and WEEK.
EXTRACT
EXTRACT gets part of a date.
SELECT EXTRACT(MONTH FROM '2012-08-20');
SELECT EXTRACT(YEAR FROM order_date) AS order_year
FROM orders;DATEDIFF
DATEDIFF calculates the difference between two dates.
DATEDIFF(date1, date2)Example:
SELECT DATEDIFF('2017-01-13', '2017-01-03') AS DateDiff;CURDATE
CURDATE returns the current date.
SELECT *
FROM goods
WHERE expiry_date = CURDATE();