SQL Server DATEADD CLI Reference: Syntax & Troubleshooting

sql server dateadd is the T-SQL function in Microsoft SQL Server that adds or subtracts a specified number of time units to a date or datetime value, enabling calendar arithmetic and deadline calculations. It is part of the built-in date/time functions and supports all dateparts from nanoseconds to years.

-- Basic syntax as per Microsoft Learn
DATEADD ( datepart , number , date )

Tested on SQL Server 2022 (16.x) with standard T-SQL compatibility.

The function accepts three mandatory arguments:

• datepart – the unit of time to add (e.g., year, month, day, hour, minute, second, nanosecond).
• number – an integer (or bigint) value specifying the interval to add. Negative values subtract.
• date – the base date expression; can be a column, literal, variable, or expression that resolves to a date/time type.

sql server dateadd Rapid Reference Cheat Sheet

Advanced Implementation & Parameters

Datepart argument

SQL Server supports all valid datepart abbreviations and full names. The full list from Microsoft documentation:

Note: Using weekday adds the same number of days as day; it does not adjust to business days.

Number argument behavior

The number parameter accepts data types int and bigint. Fractional values are truncated (not rounded). For very large numbers (e.g., adding 1,000,000 days), DATEADD may return NULL or raise Msg 517 (adding to a smalldatetime beyond its range) or Msg 8115 (arithmetic overflow). Using bigint for the number parameter helps avoid overflow in intermediate calculations.

Date argument and return types

DATEADD returns the same data type as the date argument, unless the input is a string literal—then the output is datetime. For smalldatetime inputs, any datepart finer than minute (i.e., second, millisecond) is ignored and returns smalldatetime with seconds set to 00.

-- smalldatetime truncates seconds
SELECT DATEADD(second, 30, CAST('2024-01-01 12:00:00' AS smalldatetime));
-- Output: 2024-01-01 12:00:00 (no change)

Examples from official documentation

-- A. Increment datepart by an interval of 1
SELECT DATEADD(year, 1, '2024-01-01');

-- B. Increment more than one level in one statement
SELECT DATEADD(month, 18, '2024-01-01');   -- adds 1 year + 6 months

-- C. Use expressions as arguments
DECLARE @days int = 10;
SELECT DATEADD(day, @days, GETDATE());

-- Specify a column as date
SELECT EmployeeID, DATEADD(day, -30, HireDate) AS ProbationEnd
FROM Employees;

Error Resolution & Troubleshooting

SELECT DATEADD(second, 1, CAST('2079-06-06' AS datetime));

SELECT DATEADD(day, 1000000, CAST('2024-01-01' AS datetime2));

SELECT DATEADD(day, ISNULL(@days, 0), GETDATE());

Production-Grade Implementation

• Always use four-digit years to avoid ambiguity with the two-digit year cutoff server configuration (SELECT SERVERPROPERTY('TwoDigitYearCutoff')).
• Prefer datetime2 over datetime or smalldatetime for new development. datetime2 offers a larger range (0001-01-01 to 9999-12-31) and higher fractional second precision (7 digits), reducing overflow and truncation.
• Use bigint for the number parameter when adding extremely large intervals or when the number may exceed int range (2,147,483,647).
• Cache static date calculations in computed columns or indexed views when possible, instead of recomputing DATEADD in every query.
• Test edge cases around month boundaries and leap years. For example, DATEADD(month, 1, '2024-01-31') returns 2024-02-29 (last day of February), not 2024-02-28.
• Avoid using DATEADD directly in WHERE clauses on large tables without sargability. Instead, compute the boundary date first and filter against the column.

SELECT DATEADD(day, 7, GETDATE()) AS OneWeekLater;
SELECT DATEDIFF(day, '2025-01-01', '2025-12-31') AS DaysInYear;

-- Get first day of next month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) AS NextMonthStart;

SELECT DATEADD(second, 1000000000, CAST(GETDATE() AS DATETIME2));
-- Or use DATEADD(hour, n, date) for larger intervals implicitly

-- Example on RDS
SELECT DATEADD(day, -30, GETDATE()) AS ThirtyDaysAgo;

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS FirstOfMonth;
-- Equivalent but slightly slower:
SELECT DATEADD(day, 1-DAY(GETDATE()), CAST(GETDATE() AS DATE));