DATEADD()

Syntax

How it works

DATEADD() shifts a date forward or backward by a whole number of units. In SQL Server the call order is DATEADD(datepart, number, date), so DATEADD(day, 30, order_date) returns the date thirty days after order_date. The datepart comes first and is a bare keyword such as day, month or year, not a quoted string.

The name DATEADD is specific to SQL Server. MySQL has no DATEADD function at all: it uses DATE_ADD(date, INTERVAL number unit), for example DATE_ADD(order_date, INTERVAL 30 DAY), or the shorthand order_date + INTERVAL 30 DAY. PostgreSQL does not have DATEADD either; it relies on native interval arithmetic, order_date + INTERVAL '30 days'. All three produce the same result, they just spell it differently.

To subtract time you do not need a separate function. Pass a negative number in SQL Server (DATEADD(day, -30, ...)), a negative interval in MySQL (INTERVAL -30 DAY or use DATE_SUB), or subtract the interval in PostgreSQL (order_date - INTERVAL '30 days'). To compute the gap between two dates instead of shifting one, use DATEDIFF(). See the SQL data types guide for how date and datetime values are stored.

Examples

Add 30 days in SQL Server

-- SQL Server: datepart first, then number, then date
SELECT DATEADD(day, 30, '2026-01-15') AS due_date;

due_date
----------
2026-02-14

Add 30 days in MySQL and PostgreSQL

-- MySQL
SELECT DATE_ADD('2026-01-15', INTERVAL 30 DAY) AS due_date;

-- MySQL shorthand
SELECT '2026-01-15' + INTERVAL 30 DAY AS due_date;

-- PostgreSQL
SELECT DATE '2026-01-15' + INTERVAL '30 days' AS due_date;

due_date
----------
2026-02-14

Add months and years

-- SQL Server
SELECT DATEADD(month, 3, '2026-01-31') AS plus_3_months,
       DATEADD(year,  1, '2026-01-31') AS plus_1_year;

plus_3_months | plus_1_year
--------------+------------
 2026-04-30   | 2027-01-31

Subtract time with a negative number

-- SQL Server: 7 days ago
SELECT DATEADD(day, -7, '2026-07-05') AS week_ago;

-- MySQL equivalent
SELECT DATE_ADD('2026-07-05', INTERVAL -7 DAY) AS week_ago;

-- PostgreSQL equivalent
SELECT DATE '2026-07-05' - INTERVAL '7 days' AS week_ago;

week_ago
----------
2026-06-28

Add hours to the current timestamp

-- SQL Server
SELECT DATEADD(hour, 2, CURRENT_TIMESTAMP) AS in_2_hours;

-- MySQL
SELECT CURRENT_TIMESTAMP + INTERVAL 2 HOUR AS in_2_hours;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP + INTERVAL '2 hours' AS in_2_hours;

in_2_hours
-------------------
2026-07-05 14:30:00

Expiry date computed in a query

-- Subscriptions expire 1 year after they start (SQL Server)
SELECT id,
       start_date,
       DATEADD(year, 1, start_date) AS expires_on
FROM subscriptions;

id | start_date | expires_on
---+------------+-----------
 1 | 2026-03-01 | 2027-03-01
 2 | 2026-05-20 | 2027-05-20

Common mistakes

-- Using SQL Server argument order in MySQL,
-- or quoting the datepart as a string
SELECT DATEADD('day', 30, order_date) FROM orders;   -- SQL Server: wrong, 'day' is quoted
SELECT DATEADD(order_date, INTERVAL 30 DAY) FROM orders; -- MySQL: no such function

-- SQL Server: datepart is a bare keyword, date is last
SELECT DATEADD(day, 30, order_date) FROM orders;

-- MySQL: date first, then the INTERVAL expression
SELECT DATE_ADD(order_date, INTERVAL 30 DAY) FROM orders;

-- Wrapping the indexed column in DATEADD forces a
-- full scan: the index on order_date cannot be used
SELECT *
FROM orders
WHERE DATEADD(day, 30, order_date) >= CURRENT_TIMESTAMP;

-- Keep the column bare and shift the constant instead,
-- so the index on order_date stays usable
SELECT *
FROM orders
WHERE order_date >= DATEADD(day, -30, CURRENT_TIMESTAMP);

-- Expecting DATEADD to exist in PostgreSQL
SELECT DATEADD(day, 30, order_date) FROM orders;

-- PostgreSQL uses interval arithmetic
SELECT order_date + INTERVAL '30 days' FROM orders;

Performance

The single most important performance rule with DATEADD() is to keep it off indexed columns inside WHERE and JOIN conditions. A predicate like WHERE DATEADD(day, 30, order_date) >= @now is non-sargable: the engine must compute the expression for every row, so it scans the whole table even when a perfectly good index on order_date exists. Rewrite it as a range against the bare column, WHERE order_date >= DATEADD(day, -30, @now), and the index can seek directly to the matching rows.

The rewrite works because arithmetic on a constant is evaluated once, before the scan begins, while arithmetic on a column is evaluated per row. The same principle applies in MySQL (order_date >= NOW() - INTERVAL 30 DAY) and PostgreSQL (order_date >= now() - INTERVAL '30 days'). See the indexing guide for why sargable predicates matter.

DATEADD() itself is cheap; it is a simple arithmetic operation on a value the engine already holds. The cost only appears when it prevents an index from being used or when it is called billions of times in a large scan. Computing it in the SELECT list, for reporting columns, has negligible cost.

Interview questions

-- SQL Server
SELECT DATEADD(day, 30, '2026-01-15');
-- MySQL
SELECT DATE_ADD('2026-01-15', INTERVAL 30 DAY);
-- PostgreSQL
SELECT DATE '2026-01-15' + INTERVAL '30 days';

SELECT DATEADD(day, -7, '2026-07-05') AS week_ago;

-- Sargable rewrite
SELECT * FROM orders
WHERE order_date >= DATEADD(day, -30, CURRENT_TIMESTAMP);