How to Use DATE_ADD in MySQL: Syntax and Examples

In MySQL, use the DATE_ADD(date, INTERVAL value unit) function to add a specific time interval to a date. For example, DATE_ADD('2024-06-01', INTERVAL 5 DAY) adds 5 days to the given date.

Syntax

The DATE_ADD function adds a time interval to a date or datetime value.

• date: The starting date or datetime.
• INTERVAL value unit: The amount and type of time to add (e.g., days, months, years).

sql

DATE_ADD(date, INTERVAL value unit)

Example

This example adds 10 days to the date '2024-06-01'. It shows how DATE_ADD returns the new date after adding the interval.

sql

SELECT DATE_ADD('2024-06-01', INTERVAL 10 DAY) AS new_date;

Output

new_date
2024-06-11

Common Pitfalls

Common mistakes include:

• Forgetting to use the INTERVAL keyword before the value and unit.
• Using incorrect units like 'days' instead of 'DAY'.
• Passing invalid date formats.

Always use uppercase units like DAY, MONTH, YEAR.

sql

/* Wrong usage: missing INTERVAL keyword */
SELECT DATE_ADD('2024-06-01', 10 DAY);

/* Correct usage */
SELECT DATE_ADD('2024-06-01', INTERVAL 10 DAY);

Quick Reference

Unit	Description	Example
SECOND	Add seconds	INTERVAL 30 SECOND
MINUTE	Add minutes	INTERVAL 15 MINUTE
HOUR	Add hours	INTERVAL 5 HOUR
DAY	Add days	INTERVAL 10 DAY
WEEK	Add weeks	INTERVAL 2 WEEK
MONTH	Add months	INTERVAL 3 MONTH
YEAR	Add years	INTERVAL 1 YEAR

Key Takeaways

Use DATE_ADD(date, INTERVAL value unit) to add time intervals to dates in MySQL.

Always include the INTERVAL keyword before the value and unit.

Use uppercase time units like DAY, MONTH, YEAR for correct syntax.

DATE_ADD works with date and datetime values to return a new date.

Common errors come from missing INTERVAL or using invalid units.