SQL Server DATEADD Function: Syntax, Parameters, and Examples

ms sql dateadd is the Transact-SQL function for date-time arithmetic, adding an integer interval to a specified datepart (year, month, day, etc.) and returning the computed datetime value, commonly used in SQL Server 2017-2022.

-- Add 10 days to current date
SELECT DATEADD(day, 10, GETDATE()) AS FutureDate;

ms sql dateadd Syntax Reference

The canonical syntax for DATEADD in T-SQL (Microsoft SQL Server, Azure SQL Database) is:

DATEADD ( datepart , number , date )

Parameters:

• datepart — the part of date to which number is added (e.g., year, month, day, hour, minute, second, millisecond, microsecond, nanosecond).
• number — an expression that resolves to a bigint; value to add (positive for future, negative for past).
• date — an expression that evaluates to a date/time value (string literal, column, variable, or function like GETDATE()).

Return type depends on the input date data type. If date is a string literal, output is datetime; if datetime2, output is datetime2.

-- Subtract 3 months from a specific date
SELECT DATEADD(month, -3, '2025-06-15') AS PastDate;

-- Add 1 year and 2 months (single levels only)
SELECT DATEADD(year, 1, DATEADD(month, 2, GETDATE()));

ms sql dateadd Rapid Reference Cheat Sheet

Tested on SQL Server 2022 (16.0.1000.6).

Advanced Implementation & Parameters

Datepart Argument (datepart)

SQL Server recognizes both full name and abbreviations. Full list:

• year (yy, yyyy)
• quarter (qq, q)
• month (mm, m)
• dayofyear (dy, y) — adds days to the date (same as day) but logical grouping differs.
• day (dd, d)
• week (wk, ww) — increments by 7-day intervals.
• weekday (dw, w) — adds number of weekdays (Sunday=1).
• hour (hh)
• minute (mi, n)
• second (ss, s)
• millisecond (ms)
• microsecond (mcs) — only supported when date is datetime2 or datetimeoffset.
• nanosecond (ns) — same restriction, fractional seconds of nanosecond precision.

Number Argument (number)

Accepts bigint; values can be negative. Fractional decimals are truncated (not rounded). For example, DATEADD(day, 2.7, '2025-01-01') adds 2 days (truncates .7). Use CAST(number AS int) if integer is guaranteed.

Date Argument (date)

Can be any expression that evaluates to a date/time type: GETDATE(), column names, string literals (e.g., '2025-06-15 14:30:00.123'), CURRENT_TIMESTAMP, SYSDATETIME(). When using string literals, implicit conversion occurs; for fractional seconds, provide enough digits to avoid truncation.

Return Types and Behavior

Fractional Seconds Precision: Adding millisecond to a datetime yields 3 decimal places. For higher precision, use datetime2. smalldatetime does not support seconds or fractional seconds — attempt causes Msg 517.

-- High-precision example from official docs
DECLARE @datetime2 AS DATETIME2 = '2024-01-01 13:10:10.1111111';
SELECT '1 millisecond', DATEADD(millisecond, 1, @datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond, 2, @datetime2);

Error Resolution & Troubleshooting

-- Cast to datetime or datetime2 before DATEADD
SELECT DATEADD(second, 10, CAST(@smalldatetime AS datetime));

-- Use datetime2 (range 0001-9999) for larger offsets
SELECT DATEADD(year, 10000, CAST('2025-01-01' AS datetime2));

-- Use unambiguous format yyyy-mm-dd or yyyy-mm-ddThh:mi:ss.mmm
SELECT DATEADD(day, 1, '2025-12-31');

Also note that DATEADD does not accept user-defined data types or XML; always pass a date/time type.

Production-Grade Implementation

• Use explicit datepart names (e.g., day instead of dd) to improve readability and avoid ambiguity. Abbreviated forms are supported but full names are clearer.
• Avoid adding days via simple addition (date + 360) — this relies on internal datetime integer representation and breaks for datetime2. Always use DATEADD for clarity and portability.
• For large intervals, chain multiple DATEADD calls rather than converting to a single component (e.g., 365 days = 1 year + 0 days; use DATEADD(year, 1, DATEADD(day, 0, @date))).
• Use SYSDATETIME() instead of GETDATE() when working with fractional seconds, because GETDATE() returns datetime with 3 ms precision. SYSDATETIME() returns datetime2(7).
• Handle time zone offset with datetimeoffset: DATEADD works on the date/time component only; the offset remains unchanged. For shifting time zones, use SWITCHOFFSET.
• Performance: DATEADD is deterministic and sargable when used in WHERE clauses. For large datasets, avoid wrapping a column in DATEADD on the field — instead compute the offset on the constant side (e.g., WHERE OrderDate > DATEADD(day, -30, GETDATE())).