DATEDIFF & DATE_ADD

DATEDIFF counts how many days lie between two dates, like a countdown to your birthday, and DATE_ADD moves a date forward by an amount you choose.

In this page:

Finding Days Between with DATEDIFF
Adding Days with DATE_ADD
Subtracting Days with DATE_SUB
Time Calculations
Calculating Age or Time Left

Syntax

sql

DATEDIFF(end_date, start_date)
DATE_ADD(date, INTERVAL value unit)
DATE_SUB(date, INTERVAL value unit)

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

sql

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

sql

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

sql

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

sql

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

sql

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:

← Previous Lesson DATE_FORMAT Next Lesson → YEAR MONTH DAY