DATEDIFF & DATE_ADD
In this page:
Finding Days Between with DATEDIFF
DATEDIFF calculates the whole number of days between two dates, returning a negative number if the first date comes before the second — a quick way to measure things like how many days overdue an invoice is.
Example: Finding Days Between with DATEDIFF
SELECT DATEDIFF('2024-06-15', '2024-06-01') AS days_overdue;Adding Days with DATE_ADD
DATE_ADD adds a chosen interval — days, weeks, months, or years — onto a date value, which is the standard way to compute things like a subscription renewal date or a shipping estimate.
Example: Adding Days with DATE_ADD
SELECT DATE_ADD('2024-06-01', INTERVAL 30 DAY) AS renewal_date;Subtracting Days with DATE_SUB
DATE_SUB works the same way as DATE_ADD but moves backward in time instead of forward, useful for calculating things like 'orders placed in the last 30 days' relative to today.
Example: Subtracting Days with DATE_SUB
SELECT DATE_SUB('2024-06-30', INTERVAL 30 DAY) AS thirty_days_before;Time Calculations
Both DATE_ADD and DATE_SUB can operate on full datetime values, letting you add or subtract hours, minutes, or seconds when you need finer precision than whole days.
Example: Time Calculations
SELECT DATE_ADD('2024-06-01 10:00:00', INTERVAL 90 MINUTE) AS meeting_end;Calculating Age or Time Left
Combining DATEDIFF with NOW() gives you a live countdown, such as how many days remain until a deadline or how many days have passed since a customer's last order, calculated fresh on every query.
Example: Calculating Age or Time Left
CREATE TABLE customers (id INT, last_order_date DATE);
INSERT INTO customers VALUES (1, '2024-05-01');
SELECT id, DATEDIFF(NOW(), last_order_date) AS days_since_last_order FROM customers;Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: