DATE_SUB

Syntax

DATE_SUB (dateobj, INTERVAL expr unit)
SUBDATE (dateobj, INTERVAL expr unit)
SUBDATE (dateobj, days)

Arguments

• dateobj: a valid date, datetime, or parsable date string

• expr: the number of units to add if unit is a simple type, or a string representation of the units to add if unit is a complex type. Can be negative.

• days: number of days to subtract. Can be negative.

See DATE_ADD for a description of unit.

Return Type

Date or datetime object. If dateobj is not a valid date, returns NULL.

Remarks

• In DATE_SUB() and SUBDATE() functions, MOD(...) and a MOD b expressions in INTERVAL expr unit syntax are not supported without parentheses due to parser limitations. Use the modulo operator (%), or wrap the MOD expression in parentheses.

  SQL

  Copy

  -- Supported
  SELECT DATE_SUB('2020-01-01', INTERVAL 100 % 3 DAY);
  SELECT DATE_SUB('2020-01-01', INTERVAL (MOD(100, 3)) DAY);
  SELECT DATE_SUB('2020-01-01', INTERVAL (100 MOD 3) DAY);
  
  -- Not supported
  SELECT DATE_SUB('2020-01-01', INTERVAL MOD(100, 3) DAY);
  SELECT DATE_SUB('2020-01-01', INTERVAL 100 MOD 3 DAY);

Examples

SELECT DATE_SUB('2010-04-02', INTERVAL 1 WEEK);

+-----------------------------------------+
| DATE_SUB("2010-04-02", INTERVAL 1 WEEK) |
+-----------------------------------------+
| 2010-03-26                              |
+-----------------------------------------+