Add or Subtract Days, Months & Years in SQL (per Dialect)

Shift a date forward or back by an interval.

Add 7 days

-- MySQL
SELECT DATE_ADD(d, INTERVAL 7 DAY);     -- or d + INTERVAL 7 DAY

-- PostgreSQL
SELECT d + INTERVAL '7 days';

-- BigQuery
SELECT DATE_ADD(d, INTERVAL 7 DAY);

-- Snowflake
SELECT DATEADD(day, 7, d);

-- SQLite
SELECT DATE(d, '+7 days');

-- DuckDB
SELECT d + INTERVAL 7 DAY;

Subtract 1 month / add 1 year

SELECT DATE_SUB(d, INTERVAL 1 MONTH);   -- MySQL
SELECT d - INTERVAL '1 month';          -- PostgreSQL / DuckDB
SELECT DATE_SUB(d, INTERVAL 1 MONTH);   -- BigQuery
SELECT DATEADD(month, -1, d);           -- Snowflake
SELECT DATE(d, '-1 month');             -- SQLite

SELECT DATE_ADD(d, INTERVAL 1 YEAR);    -- MySQL / BigQuery
SELECT d + INTERVAL '1 year';           -- Postgres / DuckDB
SELECT DATEADD(year, 1, d);             -- Snowflake
SELECT DATE(d, '+1 year');              -- SQLite

Gotchas: Postgres returns timestamp when you add an interval to a timestamp and date when you add a whole-day interval to a date — cast if you need a specific type. Month math clamps: adding 1 month to Jan 31 gives Feb 28/29 in every dialect (it won’t roll into March). BigQuery’s DATE_ADD only accepts a single unit (INTERVAL 7 DAY, not INTERVAL '1 2:0:0'); use DATETIME_ADD/TIMESTAMP_ADD for time parts. SQLite chains modifiers: DATE(d, '+1 month', '-1 day').