SQL DATEADD: Add Days, Months, Years to a Date (All Databases)

Overview

Adding or subtracting days, months, or years from a date is different in every database. This guide covers the exact syntax for each.

MySQL

-- DATE_ADD / DATE_SUB:
SELECT DATE_ADD('2026-03-12', INTERVAL 7 DAY);     -- '2026-03-19'
SELECT DATE_ADD('2026-03-12', INTERVAL 3 MONTH);   -- '2026-06-12'
SELECT DATE_SUB('2026-03-12', INTERVAL 1 YEAR);    -- '2025-03-12'

-- + INTERVAL shorthand:
SELECT '2026-03-12' + INTERVAL 30 DAY;
SELECT NOW() - INTERVAL 24 HOUR;

-- Practical: orders from last 7 days:
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;

PostgreSQL

-- INTERVAL arithmetic:
SELECT '2026-03-12'::DATE + INTERVAL '7 days';    -- 7 days later
SELECT '2026-03-12'::DATE + INTERVAL '3 months';  -- 3 months later
SELECT '2026-03-12'::DATE - INTERVAL '1 year';    -- 1 year ago

-- Or simply add integers (adds days):
SELECT '2026-03-12'::DATE + 7;  -- '2026-03-19'

-- Last 30 days:
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

-- make_interval:
SELECT NOW() + make_interval(months => 3);

SQL Server

-- DATEADD(part, number, date):
SELECT DATEADD(DAY, 7, '2026-03-12');     -- 7 days later
SELECT DATEADD(MONTH, 3, '2026-03-12');   -- 3 months later
SELECT DATEADD(YEAR, -1, '2026-03-12');   -- 1 year ago
SELECT DATEADD(HOUR, -24, GETDATE());     -- 24 hours ago

-- Parts: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

-- Last 7 days:
SELECT * FROM orders
WHERE created_at >= DATEADD(DAY, -7, GETDATE());

Oracle

-- Add days (simple arithmetic):
SELECT SYSDATE + 7 FROM DUAL;              -- 7 days later
SELECT SYSDATE - 30 FROM DUAL;             -- 30 days ago

-- ADD_MONTHS:
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;   -- 3 months later
SELECT ADD_MONTHS(SYSDATE, -12) FROM DUAL;  -- 1 year ago

-- INTERVAL:
SELECT SYSDATE + INTERVAL '7' DAY FROM DUAL;
SELECT SYSDATE + INTERVAL '3' MONTH FROM DUAL;

Skip the Syntax Lookup

Instead of memorizing DATEADD / DATE_ADD / INTERVAL syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions