DATE_SUB()
Subtracts an interval from a date or timestamp.
Description
DATE_SUB shifts a date or timestamp backward by a given interval — go back a
day, a month, a year. Like DATE_ADD, it uses calendar-aware interval math so
leap years and month lengths are handled correctly.
Syntax
DATE_SUB(date, INTERVAL '1 month')
DATE_SUB(date, INTERVAL '7 days')Parameters
| Name | Description | Optional |
|---|---|---|
| date | The starting date/timestamp. | No |
| interval | The interval to subtract (e.g. INTERVAL '1 year'). | No |
Return Type
Returns the same type as the input.
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
DATE_SUB vs DATE_ADD
DATE_SUB(d, INTERVAL '1 month') is the same as DATE_ADD(d, INTERVAL '-1 month'). Use whichever reads clearer.
Common Mistakes
- Subtracting raw numbers. Use intervals, not
date - 30, for correct calendar behavior. - Leap years. Intervals handle Feb 29; raw arithmetic doesn't.
- Type mismatch. Mixing DATE and timestamp intervals can change the result type.
Related Functions
See also: DATE_ADD, DATEDIFF, DATE_TRUNC.