MySQL DATE_SUB() Function

Summary: in this tutorial, you will learn how to subtract a time from a date using the MySQL DATE_SUB() function.

Introduction to MySQL DATE_SUB() function

The DATE_SUB() function subtracts a time value (or an interval) from a DATE or DATETIME value.

Here’s the syntax of the DATE_SUB() function:

DATE_SUB(date,INTERVAL expr unit)Code language: SQL (Structured Query Language) (sql)

The DATE_SUB() function accepts two arguments:

•  date: This is the date that you want to subtract a value.
•  expr: This is a string that determines an interval value that you want to subtract from the date. The unit is the interval unit that expr should be interpreted e.g., DAY, HOUR, etc.

The DATE_SUB() function returns NULL if the date is NULL.

The following statement uses the DATE_SUB() function to subtract one day from the July-4th-2017:

SELECT DATE_SUB('2017-07-04',INTERVAL 1 DAY) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| result     |
+------------+
| 2017-07-03 |
+------------+
1 row in set (0.00 sec) Code language: JavaScript (javascript)

In this example:

• The date is 2017-07-04, which is in the yyyy-mm-dd format.
• The INTERVAL 1 DAY is interpreted as 1 day interval.

The DATE_SUB() function returns a string value that represents the date July, 3rd 2017

Similar to the DATE_ADD() function, the data type of the return value of the DATE_SUB() function can be:

• a DATETIME value if the first argument is a DATETIME or the interval has time elements such as the hour, minute, second, etc.
• a string otherwise.

See the following example:

SELECT DATE_SUB('2017-07-04',INTERVAL 3 HOUR) result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| result              |
+---------------------+
| 2017-07-03 21:00:00 |
+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Because the interval is 3 hours, the result of the DATE_SUB function is a DATETIME value.

Negative interval

The expr in the interval can be positive or negative. In case the expr is negative, the DATE_SUB() function behaves like the DATE_ADD() function as shown in the following example:

SELECT DATE_SUB('2017-07-03',INTERVAL -1 DAY) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| result     |
+------------+
| 2017-07-04 |
+------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Invalid or malformed date

If the first argument of the DATE_SUB() function is a malformed, invalid date, or NULL, the DATE_SUB() function returns NULL.

SELECT DATE_SUB('2017-02-29', INTERVAL - 1 DAY) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set, 1 warning (0.00 sec)
Code language: JavaScript (javascript)

In this example, 2017-02-03 is an invalid date, therefore, the result is NULL. In addition, MySQL produced a warning.

SHOW WARNINGS;

Output:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-29' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The following examples demonstrate the effects when passing a malformed date or NULL to the DATE_SUB function:

SELECT DATE_SUB('03/07/2017', INTERVAL 1 DAY) result;Code language: JavaScript (javascript)

Output:

+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

SELECT DATE_SUB(NULL, INTERVAL 1 DAY) result;Code language: PHP (php)

Output:

+--------+
| result |
+--------+
| NULL   |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

MySQL DATE_SUB: automatically adjusted day

If you subtract MONTH, YEAR, or YEAR_MONTH from a date and the new date has a day that’s too big for the new month, the DATE_SUB() will adjust the day to the latest day of the new month. For example:

SELECT DATE_SUB('2017-03-30', INTERVAL 1 MONTH) result;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+
| result     |
+------------+
| 2017-02-28 |
+------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

In this example, we subtracted 1 month from March 30th 2017, therefore, the result is February 28th 2017.

However, the DATE_SUB() function adjusted the day to 28 instead of 30 because February 2017 has 28 days only.

Summary

• Use the MySQL DATE_SUB() function to subtract an interval from a DATE or DATETIME value.