How to Use DATEADD Function

DATEADD adds a whole number of date or time units to a value and returns the new date. Use a negative number to subtract:

DATEADD ( datepart , number , date )

SELECT DATEADD(day,   30, '2026-10-08') AS Plus30Days,     -- 2026-11-07 00:00:00.000
       DATEADD(month, -3, '2026-10-08') AS Minus3Months,   -- 2026-07-08 00:00:00.000
       DATEADD(hour,  -2, SYSDATETIME()) AS TwoHoursAgo;

There is no DATESUB in SQL Server; subtraction is DATEADD with a negative number.

The three arguments

datepart is the unit. It is a keyword, not a string, so it cannot be passed in a variable or parameter.

datepart	Abbreviations		datepart	Abbreviations
year	yy, yyyy		hour	hh
quarter	qq, q		minute	mi, n
month	mm, m		second	ss, s
dayofyear	dy, y		millisecond	ms
day	dd, d		microsecond	mcs
week	wk, ww		nanosecond	ns
weekday	dw, w

Spell the name out. m is month and mi is minute, and mixing them up is a common bug.

day, dayofyear and weekday all add calendar days. weekday does not skip weekends; SQL Server has no built-in business-day arithmetic, so that needs a calendar table.

number is an integer. A fractional value is truncated, not rounded: DATEADD(day, 1.9, ...) adds one day. To add a day and a half, add 36 hours.

date can be a column, variable, expression or string literal of type date, datetime, datetime2, smalldatetime, datetimeoffset or time.

What type comes back

DATEADD returns the same type as the date argument, with one exception: a string literal is treated as datetime. That is why the first example shows a time portion. Cast the input when you want something else:

SELECT DATEADD(day, 30, CAST('2026-10-08' AS date));   -- 2026-11-07

The unit must make sense for the type. Adding hours to a date, or days to a time, fails:

DECLARE @d date = '2026-10-08';
SELECT DATEADD(hour, 1, @d);

Msg 9810, Level 16, State 1, Line 2
The datepart hour is not supported by date function dateadd for data type date.

microsecond and nanosecond are not allowed for datetime, smalldatetime or date. A datetime value is only accurate to about 3 milliseconds, so adding 1 millisecond to one can return the original value. Use datetime2 when you need finer precision.

Month and year arithmetic at month end

When the target month is shorter than the starting day, DATEADD returns the last day of the target month instead of overflowing into the next one. Using a small table of subscriptions:

CREATE TABLE dbo.Subscriptions (
    SubscriptionID int  NOT NULL PRIMARY KEY,
    StartDate      date NOT NULL,
    TermMonths     int  NOT NULL
);

INSERT INTO dbo.Subscriptions VALUES
(1, '2026-01-31',  1),
(2, '2026-03-15', 12),
(3, '2024-02-29', 12);

SELECT SubscriptionID,
       StartDate,
       DATEADD(month, TermMonths, StartDate)                   AS RenewalDate,
       DATEADD(day, -7, DATEADD(month, TermMonths, StartDate)) AS ReminderDate
FROM dbo.Subscriptions;

SubscriptionID  StartDate   RenewalDate  ReminderDate
1               2026-01-31  2026-02-28   2026-02-21
2               2026-03-15  2027-03-15   2027-03-08
3               2024-02-29  2025-02-28   2025-02-21

Two consequences:

• It is not reversible. January 31 plus one month is February 28, and February 28 minus one month is January 28.
• It does not stick to month end. DATEADD(month, 1, '2026-02-28') is March 28, not March 31. When you mean "the last day of next month", use EOMONTH(@d, 1).

For repeating schedules, always calculate from the original start date (DATEADD(month, n, StartDate)), not from the previous result, or a January 31 schedule drifts to the 28th for good.

Filtering by a relative date

Apply DATEADD to the constant side of the comparison, never to the column:

-- good: the cut-off is computed once and an index on StartDate can be used
SELECT SubscriptionID
FROM dbo.Subscriptions
WHERE StartDate >= DATEADD(month, -6, CAST(SYSDATETIME() AS date));

-- bad: the function runs on every row and prevents an index seek
SELECT SubscriptionID
FROM dbo.Subscriptions
WHERE DATEADD(month, 6, StartDate) >= SYSDATETIME();

For "everything in a period", use a half-open range. It works for every date type and never misses the last fraction of a second, which BETWEEN ... AND '2026-10-31 23:59:59' does:

DECLARE @start date = '2026-10-01';

SELECT SubscriptionID
FROM dbo.Subscriptions
WHERE StartDate >= @start
  AND StartDate <  DATEADD(month, 1, @start);

Useful patterns

DECLARE @dt datetime2(0) = '2026-10-08 14:35:20';

SELECT CAST(@dt AS date)                                AS StartOfDay,      -- 2026-10-08
       DATEADD(day, 1, CAST(@dt AS date))               AS StartOfTomorrow, -- 2026-10-09
       DATEADD(month, DATEDIFF(month, 0, @dt), 0)       AS StartOfMonth,    -- 2026-10-01 00:00:00.000
       EOMONTH(@dt)                                     AS EndOfMonth,      -- 2026-10-31
       DATEADD(year, DATEDIFF(year, 0, @dt), 0)         AS StartOfYear;     -- 2026-01-01 00:00:00.000

The DATEADD(unit, DATEDIFF(unit, 0, x), 0) idiom counts whole units since the base date (0 is 1900-01-01 as a datetime) and adds them back, which strips everything smaller than the unit. It works on every version. On SQL Server 2022 and later, DATETRUNC(month, @dt) is clearer and keeps the input type.

To produce a run of dates on SQL Server 2022 or later (database compatibility level 160):

SELECT DATEADD(day, value, CAST('2026-10-01' AS date)) AS TheDate
FROM GENERATE_SERIES(0, 6);          -- seven rows, Oct 1 to Oct 7

To convert a Unix timestamp in seconds to a UTC datetime2:

SELECT DATEADD(second, 1700000000, CAST('1970-01-01' AS datetime2(0)));
-- 2023-11-14 22:13:20

Limits and error messages

Number too large. Up to SQL Server 2022, number must fit in an int. A Unix timestamp in milliseconds, or a seconds value after January 2038, does not:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Split the value into two steps that each fit:

DECLARE @ms bigint = 1700000000123;

SELECT DATEADD(millisecond, @ms % 1000,
       DATEADD(second, @ms / 1000, CAST('1970-01-01' AS datetime2(3))));
-- 2023-11-14 22:13:20.123

SQL Server 2025, Azure SQL Database and Azure SQL Managed Instance accept a bigint for number, so the single call DATEADD(millisecond, @ms, ...) works there.

Result out of range. If the result falls outside the range of the data type (for datetime, before 1753 or after 9999), you get error 517:

Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused an overflow.

NULL in, NULL out. If number or date is NULL, the result is NULL.

smalldatetime has no seconds. Adding fewer than 30 seconds to a smalldatetime leaves it unchanged; larger values are rounded to the minute.

Time zones and daylight saving

DATEADD does pure calendar arithmetic. On a datetimeoffset it keeps the same offset, and it knows nothing about daylight saving time. Adding 24 hours to a local time across a clock change gives a different wall-clock time than adding one day in that time zone would. Store and calculate in UTC, then convert for display with AT TIME ZONE:

SELECT DATEADD(hour, 24, SYSUTCDATETIME())
       AT TIME ZONE 'UTC' AT TIME ZONE 'Central European Standard Time';

Common mistakes

• Wrapping the column in DATEADD inside WHERE and losing the index.
• Using m when you meant minutes, or weekday expecting business days.
• Passing a string literal and being surprised by a datetime result with a time part.
• Assuming one month forward and one month back returns the starting date.
• Using GETDATE() - 30. Integer arithmetic works only on datetime and smalldatetime; it raises an operand type clash on date and datetime2. DATEADD works on all of them.
• Passing millisecond epoch values straight to DATEADD on versions where number is an int.

Further reading

• DATEADD (Transact-SQL)
• EOMONTH (Transact-SQL)
• DATETRUNC (Transact-SQL)
• Date and time data types and functions (Transact-SQL)