MySQL: SUBDATE Function

This tutorial explains how to use the MySQL SUBDATE function with syntax and examples.

Description

The MySQL SUBDATE function returns a date after which a certain time/date interval has been subtracted.

Syntax

The syntax for the SUBDATE function in MySQL is:

SUBDATE( date, INTERVAL value unit )

OR

SUBDATE( date, days )

Parameters or Arguments

date
days
value
unit

unit	Compatibility
MICROSECOND	4.1.1+
SECOND	3.2.3+
MINUTE	3.2.3+
HOUR	3.2.3+
DAY	3.2.3+
WEEK	5+
MONTH	3.2.3+
QUARTER	5+
YEAR	3.2.3+
SECOND_MICROSECOND	4.1.1+
MINUTE_MICROSECOND	4.1.1+
MINUTE_SECOND	4.1.1+
HOUR_MICROSECOND	4.1.1+
HOUR_SECOND	4.1.1+
HOUR_MINUTE	3.2.3+
DAY_MICROSECOND	4.1.1+
DAY_SECOND	3.2.3+
DAY_MINUTE	3.2.3+
DAY_HOUR	3.2.3+
YEAR_MONTH	3.2.3+

Note

If you specify an interval value that is too short for the unit that you have specified, the SUBDATE function will assume that the left-most portion of the interval value was not provided.
The SUBDATE function (first syntax only) is a synonym for the DATE_SUB function.
Using the SUBDATE function with a negative value as a parameter is equivalent to using the ADDDATE function.
See also the DATE_ADD, DATE_SUB, ADDDATE, ADDTIME , and SUBTIME functions.

Applies To

The SUBDATE function can be used in the following versions of MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL SUBDATE function examples and explore how to use the SUBDATE function in MySQL.

For example:

mysql> SELECT SUBDATE('2014-02-13 08:44:21.000001', INTERVAL 4 MICROSECOND);
Output: '2014-02-13 08:44:20.999997'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL 20 SECOND);
Output: '2014-02-13 08:44:01'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL 25 MINUTE);
Output: '2014-02-13 08:19:21'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL 2 HOUR);
Output: '2014-02-13 06:44:21'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL 10 DAY);
Output: '2014-02-03'

mysql> SELECT SUBDATE('2014-02-13', 10);
Output: '2014-02-03'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL 12 WEEK);
Output: '2013-11-21'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL 3 MONTH);
Output: '2013-11-13'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL 3 QUARTER);
Output: '2013-05-13'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL 5 YEAR);
Output: '2009-02-13'

mysql> SELECT SUBDATE('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND);
Output: '2014-02-13 08:44:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND);
Output: '2014-02-13 08:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND);
Output: '2014-02-13 08:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND);
Output: '2014-02-13 07:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND);
Output: '2014-02-13 07:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE);
Output: '2014-02-13 07:41:21'

mysql> SELECT SUBDATE('2014-02-13 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND);
Output: '2014-02-06 07:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND);
Output: '2014-02-06 07:41:09'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE);
Output: '2014-02-06 07:41:21'

mysql> SELECT SUBDATE('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR);
Output: '2014-02-06 07:44:21'

mysql> SELECT SUBDATE('2014-02-13', INTERVAL '5-3' YEAR_MONTH);
Output: '2008-11-13'

Mysql Subdate Function