DATE_ADD / DATEDIFF

Adds an interval to a date (MySQL).

SELECT DATE_ADD(date, INTERVAL value unit);

Subtracts an interval from a date (MySQL).

SELECT DATE_SUB(date, INTERVAL value unit);

Returns the difference in days between two dates (MySQL).

SELECT DATEDIFF(date1, date2);

Returns the difference between two dates/datetimes (PostgreSQL).

SELECT AGE(date1, date2);

The following examples use the orders and members tables.

Get the date 30 days from today.

sample_date_add_datediff.sql

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY) AS deadline;

+------------+
| deadline   |
+------------+
| 2026-04-15 |
+------------+
1 row in set

Get the date 3 months before today.

sample_date_add_datediff.sql

SELECT DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH) AS three_months_ago;

+------------------+
| three_months_ago |
+------------------+
| 2025-12-16       |
+------------------+
1 row in set

Calculate the estimated delivery date, 7 days after the order date.

sample_date_add_datediff.sql

SELECT order_id, order_date,
       DATE_ADD(order_date, INTERVAL 7 DAY) AS estimated_delivery
FROM orders;

+----------+------------+--------------------+
| order_id | order_date | estimated_delivery |
+----------+------------+--------------------+
|        1 | 2025-09-01 | 2025-09-08         |
|        2 | 2025-10-05 | 2025-10-12         |
|        3 | 2025-10-15 | 2025-10-22         |
+----------+------------+--------------------+
3 rows in set

Get the number of days since each member registered.

sample_date_add_datediff.sql

SELECT member_id, name,
       DATEDIFF(CURRENT_DATE, registered_at) AS days_since_joined
FROM members;

+-----------+--------+-------------------+
| member_id | name   | days_since_joined |
+-----------+--------+-------------------+
|         1 | user_a |               519 |
|         2 | user_c |               276 |
+-----------+--------+-------------------+
2 rows in set

Get orders placed 30 or more days ago.

sample_date_add_datediff.sql

SELECT order_id, order_date
FROM orders
WHERE order_date <= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

+----------+------------+
| order_id | order_date |
+----------+------------+
|        1 | 2025-09-01 |
|        2 | 2025-10-05 |
|        3 | 2025-10-15 |
+----------+------------+
3 rows in set

In PostgreSQL, you can add an interval to a date using the + INTERVAL operator. The difference between two dates is calculated with subtraction.

-- Get the date 30 days from today (PostgreSQL).
SELECT CURRENT_DATE + INTERVAL '30 days' AS deadline;

-- Get the difference in days between two dates (PostgreSQL).
SELECT CURRENT_DATE - registered_at AS days_since_joined FROM members;

-- Get the difference in years, months, and days format (PostgreSQL).
SELECT AGE(CURRENT_DATE, registered_at) FROM members;

In Oracle, you can add days by using + number. To add months, use ADD_MONTHS.

-- Get the date 30 days from today (Oracle).
SELECT SYSDATE + 30 AS deadline FROM DUAL;

-- Get the date 3 months ago (Oracle).
SELECT ADD_MONTHS(SYSDATE, -3) AS three_months_ago FROM DUAL;

In SQL Server, use DATEADD to add an interval and DATEDIFF to find the difference.

-- Get the date 30 days from today (SQL Server).
SELECT DATEADD(DAY, 30, GETDATE()) AS deadline;

-- Get the number of days since each member registered (SQL Server).
SELECT member_id, name,
       DATEDIFF(DAY, registered_at, GETDATE()) AS days_since_joined
FROM members;

In SQLite, use the date function with modifiers to perform date calculations.

-- Get the date 30 days from today (SQLite).
SELECT date('now', '+30 days') AS deadline;

-- Get the date 3 months ago (SQLite).
SELECT date('now', '-3 months') AS three_months_ago;

DATE_ADD and DATE_SUB are MySQL-specific functions, but the standard SQL + INTERVAL syntax (e.g., order_date + INTERVAL '7' DAY) also works in PostgreSQL. If portability matters, prefer the standard SQL syntax.

DATEDIFF(date1, date2) in MySQL returns the number of days as date1 − date2. In PostgreSQL, you can use the subtraction operator (date1 - date2) to get the integer difference in days.

To retrieve the current date or datetime, see CURRENT_DATE / NOW. To extract parts such as year, month, or day from a date, see EXTRACT / FORMAT.