MySQL DATE_SUB Function
Summary: in this tutorial, you will learn how to subtract a time value 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. The following illustrates the DATE_SUB() function:
DATE_SUB(start_date,INTERVAL expr unit)
Code language: SQL (Structured Query Language) (sql)
The DATE_SUB() function accepts two arguments:
-
start_dateis the startingDATEorDATETIMEvalue. -
expris a string that determines an interval value to be subtracted from the starting date. Theunitis the interval unit thatexprshould be interpreted e.g.,DAY,HOUR, etc.
The following statement uses the DATE_SUB() function to subtract 1 day from the July-4th-2017:
SELECT DATE_SUB('2017-07-04',INTERVAL 1 DAY) result;
+------------+
| result |
+------------+
| 2017-07-03 |
+------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this example, the starting date is 2017-07-04, which is in the yyyy-mm-dd format. The INTERVAL 1 DAY is interpreted as 1 day interval. The result of the DATE_SUB() function is a string value represented July, 3rd 2017
Similar to the DATE_ADD() function, the data type of the return value of the DATE_SUB() function can be:
- a
DATETIMEvalue if the first argument is aDATETIMEor the interval has time element such as the hour, minute, second, etc. - a string otherwise.
See the following example:
SELECT DATE_SUB('2017-07-04',INTERVAL 3 HOUR) result;+---------------------+
| result |
+---------------------+
| 2017-07-03 21:00:00 |
+---------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Because the interval is 3 hours, the result of the DATE_SUB function is a DATETIME value.
MySQL DATE_SUB: 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;
+------------+
| result |
+------------+
| 2017-07-04 |
+------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
MySQL DATE_SUB: Invalid or malformed date
If the first argument of the DATE_SUB() function is malformed, invalid date, or NULL, the DATE_SUB() function returns NULL.
SELECT DATE_SUB('2017-02-29', INTERVAL - 1 DAY) result;
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set, 1 warning (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this example, 2017-02-03 is an invalid date, therefore, the result is NULL. In addition, MySQL produced a warning.
SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2017-02-29' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
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;
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set, 1 warning (0.00 sec)SELECT DATE_SUB(NULL, INTERVAL 1 DAY) result;
+--------+
| result |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
MySQL DATE_SUB: automatic adjusted day
If you subtract an interval of MONTH, YEAR, or YEAR_MONTH from a date which results in a date that has a day bigger than the maximum day for the new month, the day will be adjusted to the maximum day in the new month.
Consider the following statement:
SELECT DATE_SUB('2017-03-30', INTERVAL 1 MONTH) result;
+------------+
| result |
+------------+
| 2017-02-28 |
+------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this example, we subtracted 1 month from March 30th 2017 therefore the result is February 28th 2017. The day was adjusted to 28th instead of 30th because February 2017 has 28 days only.
In this tutorial, you have learned how to use the MySQL DATE_SUB() function to subtract an interval from a DATE or DATETIME value.