date

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

Loading playground environment...

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.

See also: DATE_ADD, DATEDIFF, DATE_TRUNC.