How to use the MySQL DATE_SUB() function

The DATE_SUB() function in MySQL is used to subtract a time/date interval from a date and return the new date. It is an alias for the SUBDATE() function.

Syntax

The syntax for DATE_SUB() is:

DATE_SUB(date, INTERVAL expr unit)

Where:

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

Examples

1. Subtract 5 days from a date:

   SELECT DATE_SUB('2023-01-15', INTERVAL 5 DAY);
   

   This would return ‘2023-01-10’.

2. Subtract 3 months from a given date:

   SELECT DATE_SUB('2023-05-10', INTERVAL 3 MONTH);
   

   This would return ‘2023-02-10’.

3. Subtract 1 year 6 months from a date:

   SELECT DATE_SUB('2024-08-15', INTERVAL 1 YEAR 6 MONTH);
   

   This would return ‘2023-02-15’.

4. Calculate a past date by subtracting weeks:

   SELECT DATE_SUB('2023-03-05', INTERVAL 2 WEEK);
   

   This returns ‘2023-02-19’, two weeks before ‘2023-03-05’.

5. Subtract 10 hours from a datetime:

   SELECT DATE_SUB('2023-01-01 12:00:00', INTERVAL 10 HOUR);
   

   This returns ‘2022-12-31 02:00:00’.

Other Similar Functions

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

So DATE_SUB() provides an easy way to subtract intervals from dates in MySQL.