How to use the MySQL DATE_ADD() function

The DATE_ADD() function in MySQL is used to add a time/date interval to a date and return a new date. It is an alias for the ADDDATE() function.

Syntax

The syntax for DATE_ADD() is:

DATE_ADD(date, INTERVAL expr unit)

Where:

• date is the start date
• expr is the amount to add
• unit is the unit like ‘DAY’, ‘WEEK’, etc.

Examples

1. Add 10 days to a date:

   SELECT DATE_ADD('2023-01-20', INTERVAL 10 DAY);
   

   This would return ‘2023-01-30’ by adding 10 days.

2. Subtract 3 months from a date:

   SELECT DATE_ADD('2023-05-15', INTERVAL -3 MONTH);
   

   This would return ‘2023-02-15’ by subtracting 3 months.

3. Add 1 year and 6 months to a date:

   SELECT DATE_ADD('2022-08-12', INTERVAL 1 YEAR 6 MONTH);
   

   This would return ‘2024-02-12’ by adding 1 year and 6 months.

4. Calculate a past date by subtracting days:

   SELECT DATE_ADD('2023-12-31', INTERVAL -15 DAY);
   

   This returns ‘2023-12-16’ by subtracting 15 days.

5. Add 4 weeks to a date:

   SELECT DATE_ADD('2023-11-05', INTERVAL 4 WEEK);
   

   This returns ‘2023-12-03’ by adding 4 weeks.

Other Similar Functions

• ADDDATE() - Same as DATE_ADD()
• DATE_SUB() - Subtract interval from date
• DATEDIFF() - Calculate date difference
• TIMESTAMPADD() - Add interval to timestamp

So DATE_ADD() provides an easy way to add and subtract intervals from dates in MySQL.