The difference between the MySQL SUBDATE() and DATE_SUB() functions is exactly the same as the difference between the ADDDATE() and DATE_ADD() functions. One function allows for two different syntax forms, whereas the other allows for just one.

This article demonstrates the difference.

Syntax

Here’s the syntax for each function.

DATE_SUB()

The DATE_SUB() syntax goes like this.

DATE_SUB(date,INTERVAL expr unit)

This accepts a date value, followed by the INTERVAL keyword and the expression and unit for which to subtract from the date supplied by the first argument.

SUBDATE()

The SUBDATE() syntax allows for two forms. You can use either of the following forms.

SUBDATE(date,INTERVAL expr unit)

Or

SUBDATE(expr,days)

The first form is exactly the same as the DATE_SUB() syntax, and when you use this syntax, the SUBDATE() function is a synonym for DATE_SUB().

However, the second syntax form is only available when using the SUBDATE() function, and it allows you to use a shorthand way to specify the number of days to subtract from the date.

Example

Here’s an example of using both functions to subtract a certain number of days from the same date.

SET @date = '2023-12-10';
SELECT 
    DATE_SUB(@date, INTERVAL 5 DAY) AS 'DATE_SUB',
    SUBDATE(@date, INTERVAL 5 DAY) AS 'SUBDATE 1',
    SUBDATE(@date, 5) AS 'SUBDATE 2';

Result:

+------------+------------+------------+
| DATE_SUB   | SUBDATE 1  | SUBDATE 2  |
+------------+------------+------------+
| 2023-12-05 | 2023-12-05 | 2023-12-05 |
+------------+------------+------------+

However, if we try to use the alternative syntax form with DATE_SUB() we get an error.

SET @date = '2023-12-10';
SELECT DATE_SUB(@date, 5) AS 'DATE_SUB 2';

Result:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5) AS 'DATE_SUB 2'' at line 1

And of course, the reason for this error is because DATE_SUB() doesn’t support that syntax.

Note that this second syntax form can only be used for subtracting days from the date. If you need to subtract any other unit (for example, months), you’ll need to use the first syntax.