DATE ADD / INTERVAL ARITHMETICFunctions
Buy Me A Coffee

Add or subtract a time interval from a date or timestamp value. Syntax varies more than almost any other operation across SQL engines.

Adds or subtracts an interval from a date or timestamp, the standard way to shift dates forward or backward. DATEDIFF in SQL Server returns the day count between dates; date functions vary significantly across databases.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all DATE_ADD(date, INTERVAL n unit) and DATE_SUB(date, INTERVAL n unit). Also supports + INTERVAL shorthand. Units: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, and compound types (SECOND_MICROSECOND, etc.).
PostgreSQL ✓ Supported all Uses + / - operators with INTERVAL literals. Interval syntax is flexible: '7 days', '1 month 2 days', '2 hours 30 minutes'. AGE(ts1, ts2) returns a human-readable interval between two timestamps. INTERVAL arithmetic is the most expressive of any engine.
SQL Server ✓ Supported all DATEADD(unit, n, date) is the only method — no INTERVAL type or + operator for dates. Units spelled out: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond.
Oracle ✓ Supported all DATE + n adds n days (integer arithmetic). For months/years use ADD_MONTHS(date, n). INTERVAL types exist for precise interval arithmetic. No DATEADD function.
SQLite ✓ Supported all datetime() and date() functions accept a modifier string. Modifiers: '+N days', '+N months', '+N years', 'start of month', 'weekday N', etc. No INTERVAL type or DATEADD function.

Details

Date arithmetic is one of the least portable areas of SQL. SQL Server requires DATEADD() — no operator shorthand. Oracle's DATE + n adds days only; months require ADD_MONTHS(). PostgreSQL's INTERVAL arithmetic is the most expressive. SQLite's modifier string approach ('now', '+7 days') is unique and surprisingly capable.

Standard Syntax

-- Standard SQL (rarely supported as-is): SELECT CURRENT_DATE + INTERVAL '7' DAY; -- Common alternatives: DATEADD(day, 7, some_date) -- SQL Server some_date + INTERVAL 7 DAY -- MySQL some_date + INTERVAL '7 days' -- PostgreSQL

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

DATE_ADD(date, INTERVAL n unit) and DATE_SUB(date, INTERVAL n unit). Also supports + INTERVAL shorthand. Units: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, and compound types (SECOND_MICROSECOND, etc.).

SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY); -- 2024-01-22 SELECT DATE_SUB('2024-01-15', INTERVAL 1 MONTH); -- 2023-12-15 SELECT '2024-01-15' + INTERVAL 7 DAY; -- shorthand SELECT NOW() + INTERVAL '2:30' HOUR_MINUTE;

PostgreSQL

Uses + / - operators with INTERVAL literals. Interval syntax is flexible: '7 days', '1 month 2 days', '2 hours 30 minutes'. AGE(ts1, ts2) returns a human-readable interval between two timestamps. INTERVAL arithmetic is the most expressive of any engine.

SELECT '2024-01-15'::date + INTERVAL '7 days'; -- 2024-01-22 SELECT NOW() - INTERVAL '1 month 2 days'; SELECT NOW() + INTERVAL '2 hours 30 minutes'; SELECT AGE('2024-06-01'::date, '2023-01-15'::date); -- 1 year 4 mons 17 days

SQL Server

DATEADD(unit, n, date) is the only method — no INTERVAL type or + operator for dates. Units spelled out: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond.

SELECT DATEADD(day, 7, '2024-01-15'); -- 2024-01-22 SELECT DATEADD(month, -1, '2024-01-15'); -- 2023-12-15 SELECT DATEADD(hour, 2, GETDATE()); SELECT DATEADD(year, 1, GETDATE());

Oracle

DATE + n adds n days (integer arithmetic). For months/years use ADD_MONTHS(date, n). INTERVAL types exist for precise interval arithmetic. No DATEADD function.

SELECT SYSDATE + 7 FROM DUAL; -- 7 days later SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; -- 1 month later SELECT ADD_MONTHS(SYSDATE, -3) FROM DUAL; -- 3 months earlier SELECT SYSDATE + INTERVAL '2' HOUR FROM DUAL;

SQLite

datetime() and date() functions accept a modifier string. Modifiers: '+N days', '+N months', '+N years', 'start of month', 'weekday N', etc. No INTERVAL type or DATEADD function.

SELECT date('2024-01-15', '+7 days'); -- 2024-01-22 SELECT date('2024-01-15', '+1 month'); -- 2024-02-15 SELECT datetime('now', '-1 year', '+3 months'); SELECT date('now', 'start of month', '+1 month', '-1 day'); -- last day of current month