How to Use DATE_SUB in MySQL: Syntax and Examples

In MySQL, use the DATE_SUB(date, INTERVAL expr unit) function to subtract a time interval from a date. It returns the date after subtracting the specified interval, such as days, months, or years, from the given date.

Syntax

The DATE_SUB function subtracts a specified time interval from a date. It takes two arguments: the starting date and the interval to subtract.

• date: The original date or datetime value.
• INTERVAL expr unit: The amount and unit of time to subtract (e.g., 5 DAY, 2 MONTH).

sql

DATE_SUB(date, INTERVAL expr unit)

Example

This example subtracts 10 days from the date '2024-06-15'. It shows how DATE_SUB returns the new date 10 days earlier.

sql

SELECT DATE_SUB('2024-06-15', INTERVAL 10 DAY) AS new_date;

Output

new_date
2024-06-05

Common Pitfalls

Common mistakes include:

• Using incorrect interval units (e.g., writing 10 DAYS instead of 10 DAY).
• Passing a non-date value as the first argument.
• Confusing DATE_SUB with SUBDATE (they behave similarly but syntax differs).

sql

/* Wrong: incorrect interval unit */
SELECT DATE_SUB('2024-06-15', INTERVAL 10 DAYS);

/* Correct: use singular unit */
SELECT DATE_SUB('2024-06-15', INTERVAL 10 DAY);

Quick Reference

Part	Description	Example
date	The starting date or datetime	'2024-06-15'
INTERVAL	Keyword to specify time interval	INTERVAL 10 DAY
expr	Number of units to subtract	10
unit	Unit of time (DAY, MONTH, YEAR, HOUR, etc.)	DAY

Key Takeaways

Use DATE_SUB(date, INTERVAL expr unit) to subtract time from a date in MySQL.

Always use singular time units like DAY, MONTH, YEAR without plural 'S'.

DATE_SUB returns a new date value; it does not change the original data.

Common units include DAY, MONTH, YEAR, HOUR, MINUTE, and SECOND.

Ensure the first argument is a valid date or datetime value.