MySQL DATE_SUB Function

 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)

The DATE_SUB() function accepts two arguments:

•  start_date is the starting DATE or DATETIME value.
•  expr is a string that determines an interval value to be subtracted from the starting date. The unit is the interval unit that expr should 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) 

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 a 1-day interval. The result of the DATE_SUB() the 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() the 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;

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

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() the 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)

MySQL DATE_SUB: Invalid or malformed date

If the first argument of the DATE_SUB() the function is malformed, an 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)

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)

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)

MySQL DATE_SUB: automatically adjusted day

If you subtract an interval of MONTH, YEAR, or YEAR_MONTH from a date that 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)

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.