MySQL DATE_SUB() Function

Syntax

DATE_SUB(date, INTERVAL expr unit)

DATE_SUB subtracts a time interval from a date or datetime and returns the adjusted value.

Basic Example

SELECT DATE_SUB('2026-05-17', INTERVAL 7 DAY) AS minus_7_days;
-- Result: 2026-05-10

Practical query example

SELECT order_id, created_at
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Useful interval units

SELECT DATE_SUB('2026-05-17 09:30:00', INTERVAL 2 HOUR) AS minus_2_hours,
       DATE_SUB('2026-05-17', INTERVAL 1 MONTH) AS minus_1_month,
       DATE_SUB('2026-05-17', INTERVAL 1 YEAR) AS minus_1_year;

When to use it

Use DATE_SUB for rolling windows, lookback filters, SLA checks, and retention boundaries in MySQL analytics and application queries.

Common mistakes

• Forgetting the INTERVAL keyword before value and unit.
• Using unsupported unit names or typos in the unit token.
• Using DATE_SUB when addition was intended. Use DATE_ADD for forward offsets.

Cross-dialect alternatives

• PostgreSQL: date_col - INTERVAL '7 days'
• SQL Server: DATEADD(day, -7, date_col)
• Oracle: date_col - INTERVAL '7' DAY

Troubleshoot MySQL DATE_SUB problems

Related SQL Functions

• MySQL DATE_ADD() Function
• MySQL DATEDIFF() Function
• MySQL TIMESTAMPDIFF() Function
• MySQL DATE_FORMAT() Function
• MySQL CURDATE() Function
• MySQL NOW() Function

FAQ

Can DATE_SUB subtract months and years?

Yes. You can use units such as MONTH and YEAR in the INTERVAL clause.

Can I add dates with DATE_SUB?

You can pass a negative interval, but most teams use DATE_ADD for clarity.

Does DATE_SUB work with DATETIME values?

Yes. It works with DATE, DATETIME, and TIMESTAMP values and returns the adjusted value.