SUBDATE: Subtract values from given date

SUBDATE(expr1, expr2):

Used to decrement the date in expr1.

expr1: It should be of the format YYYY-MM-DD.

Expr2: It should be of the format ‘INTERVAL number DAY/MONTH/YEAR’.

Decrement 10 days to the date 2016-06-21.

mysql> SELECT SUBDATE('2016-06-21', INTERVAL 10 DAY);
+----------------------------------------+
| SUBDATE('2016-06-21', INTERVAL 10 DAY) |
+----------------------------------------+
| 2016-06-11                             |
+----------------------------------------+
1 row in set (0.00 sec)

Decrement 10 months to the date 2016-06-21.

mysql> SELECT SUBDATE('2016-06-21', INTERVAL 10 MONTH);
+------------------------------------------+
| SUBDATE('2016-06-21', INTERVAL 10 MONTH) |
+------------------------------------------+
| 2015-08-21                               |
+------------------------------------------+
1 row in set (0.00 sec)

Decrement 10 years to the date 2016-06-21.

mysql> SELECT SUBDATE('2016-06-21', INTERVAL 10 YEAR);
+-----------------------------------------+
| SUBDATE('2016-06-21', INTERVAL 10 YEAR) |
+-----------------------------------------+
| 2006-06-21                              |
+-----------------------------------------+
1 row in set (0.00 sec)

You can also provide negative numbers to increment the date.

mysql> SELECT SUBDATE('2016-06-21', INTERVAL -10 DAY);
+-----------------------------------------+
| SUBDATE('2016-06-21', INTERVAL -10 DAY) |
+-----------------------------------------+
| 2016-07-01                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('2016-06-21', INTERVAL -10 MONTH);
+-------------------------------------------+
| SUBDATE('2016-06-21', INTERVAL -10 MONTH) |
+-------------------------------------------+
| 2017-04-21                                |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBDATE('2016-06-21', INTERVAL -10 YEAR);
+------------------------------------------+
| SUBDATE('2016-06-21', INTERVAL -10 YEAR) |
+------------------------------------------+
| 2026-06-21                               |
+------------------------------------------+
1 row in set (0.00 sec)

Previous                                                 Next                                                 Home