DATE_SUB()
SQL · Date Functions
Theory
DATE_SUB moves a date backward by an interval. DATE_SUB('2026-11-08', INTERVAL 7 DAY) gives 2026-11-01. It is the mirror image of DATE_ADD and accepts the same units: days, weeks, months, years and, on timestamps, hours or minutes.
DATE_SUB is MySQL (and BigQuery) spelling. In SQL Server and Snowflake you write DATEADD(day, -7, date) with a negative number. PostgreSQL uses date - INTERVAL '7 days', Oracle date - 7, and SQLite date(d, '-7 days'). Two classic uses: computing a reminder date (send a notice 3 days before due_date) and building the start of a lookback window, like WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) for 'last 30 days'. Be clear whether the window should include today and the boundary day, because off-by-one errors are the most common mistake here.
Why we use it
Many questions look backwards: last 30 days of sales, 3-day reminders before a deadline, data older than a year to archive. DATE_SUB computes those earlier dates correctly on the calendar.
When to use it
Use it for lookback windows, reminder dates and retention cutoffs. If your database lacks DATE_SUB, add a negative interval. For the number of days between two dates use DATEDIFF, not subtraction of intervals.
How it works
- Start with a date, e.g. due_date
- Choose how far back: 3 DAY, 1 MONTH...
- Database subtracts on the calendar
- Used as a window start? Yes: Keep rows with date >= start. No: Show it as a column.
- Return the earlier date or filtered rows
Syntax
DATE_SUB(date, INTERVAL n unit)Example
SELECT DATE_SUB(hire_date, INTERVAL 30 DAY) FROM employees;Key points
- MySQL syntax shown; mirrors DATE_ADD()
- SQLite uses date(column, '-30 days')
- SQL Server/Snowflake: DATEADD(day, -3, d); PostgreSQL: d - INTERVAL '3 days'
- Crossing month and year boundaries is handled for you (Jan 2 - 3 days = Dec 30)
- Decide whether window boundaries are inclusive to avoid off-by-one errors