Use the SQL Server DATEADD Function

DATEADD is a Transact-SQL date and time function that moves a date forward or backward by a specified interval. It answers practical questions such as: What is the payment due date 30 days after an invoice is issued? When does a 90-day trial period end? What date marks the beginning of a rolling seven-day reporting window? Instead of calculating those values in application code or entering them manually, a query can derive them from the dates already stored in the database.

The function works with SQL Server temporal values, including date, datetime, datetime2, smalldatetime, time, and datetimeoffset. The interval can be measured in units ranging from years to nanoseconds, subject to the precision and range of the input data type. A positive number moves forward in time, while a negative number moves backward.

This lesson focuses on Microsoft SQL Server and Azure SQL Transact-SQL. Other database products provide date arithmetic, but they may use different function names and syntax. Treat the examples on this page as T-SQL rather than as portable ANSI SQL.

The function has three arguments:

DATEADD(datepart, number, date)

1. datepart identifies the unit to add, such as day, month, or year.
2. number is an integer expression that tells SQL Server how many units to add. A negative value subtracts units.
3. date is the starting date or time expression. It can be a column, a variable, a system function, a typed value, or a supported character-string literal.

The datepart is a T-SQL keyword token. Do not place it in quotation marks, and do not try to supply it through a user-defined variable. For example, DATEADD(month, 1, @StartDate) is valid. A quoted word in the first argument is not. If an application must choose among units dynamically, validate the choice in the application or stored procedure and select among complete, predefined expressions.

SQL Server accepts the full datepart name or one of its documented abbreviations. The full names usually make instructional and production code easier to read.

day, dayofyear, and weekday all add the requested number of days when used with DATEADD. The name weekday does not make the function skip Saturdays, Sundays, or holidays. Business-day calculations need a calendar table or other explicit business-calendar logic.

A small typed example makes the three arguments easy to identify:

SELECT DATEADD(day, 7, CAST('2026-10-26' AS date)) AS FollowUpDate;

The datepart is day, the number is 7, and the starting value is October 26, 2026. The result is 2026-11-02. The example uses the unambiguous ISO-style form YYYY-MM-DD and explicitly converts it to date. Four-digit years and typed values make the intent clearer than language-dependent strings such as 'May 2, 99'.

The same structure works for other units:

SELECT
    DATEADD(month, 3, CAST('2026-10-26' AS date)) AS ThreeMonthsLater,
    DATEADD(year,  1, CAST('2026-10-26' AS date)) AS OneYearLater,
    DATEADD(day,  -7, CAST('2026-10-26' AS date)) AS SevenDaysEarlier;

Each expression starts from the same date. The first two use positive offsets, and the third uses a negative offset. A negative number is often clearer than trying to rewrite a past-date calculation with another function.

Most real queries apply DATEADD to column values rather than constants. Suppose an invoice table stores the issue date and the number of payment-term days for each invoice. The due date can be calculated for every row:

SELECT
    InvoiceID,
    InvoiceDate,
    PaymentTermsDays,
    DATEADD(day, PaymentTermsDays, InvoiceDate) AS DueDate
FROM dbo.Invoice;

If one invoice has a 15-day term and another has a 45-day term, the number argument can come from the row. The calculation remains set-based and does not require a separate statement for each invoice. Similar patterns can calculate a warranty expiration from a purchase date, a renewal date from a membership start date, or a follow-up time from a service event.

Whether the result should be stored is a design decision. If it is always derived from stable source values, calculating it in a query, view, or computed column avoids duplicate data. If business rules can change or the historical due date must remain exactly as originally assigned, storing the value may be appropriate. DATEADD supplies the calculation, but the data model determines where that calculation belongs.

Negative offsets are especially useful for reporting windows. The following query establishes one consistent current timestamp, calculates a boundary 30 days earlier, and returns events in the half-open interval from the boundary up to the current time:

DECLARE @Now datetime2(0) = SYSDATETIME();
DECLARE @WindowStart datetime2(0) = DATEADD(day, -30, @Now);

SELECT EventID, EventTime, EventType
FROM dbo.SystemEvent
WHERE EventTime >= @WindowStart
  AND EventTime <  @Now;

Capturing the current time once keeps both boundaries consistent. The half-open pattern uses an inclusive lower boundary and an exclusive upper boundary. It is usually safer than converting timestamps to dates or writing a final time such as 23:59:59, which can miss rows stored with greater fractional-second precision.

For performance, apply DATEADD to the boundary value rather than to every value in an indexed column. A predicate such as EventTime >= DATEADD(day, -30, @Now) leaves EventTime available for an index seek more readily than wrapping the column itself in a function.

DATEADD has a dynamic return type. When the date argument is a typed expression, the result generally has that same data type. A date input returns date; a datetime2 input returns datetime2; and a datetimeoffset input returns datetimeoffset. This behavior helps a calculation retain the precision and capabilities of its source.

A character-string date literal is a special case: DATEADD returns datetime. Relying on implicit conversion can therefore produce a different result type than expected. Explicit conversion documents the intended type and avoids ambiguity:

SELECT
    SQL_VARIANT_PROPERTY(
        DATEADD(day, 1, CAST('2026-10-26' AS date)),
        'BaseType'
    ) AS ResultType;

This query reports date because the third argument is explicitly typed as date. Choose the input type deliberately when fractional seconds, time-zone offsets, or a broad date range matter.

Adding months requires a calendar rule because months do not all contain the same number of days. If the original day does not exist in the destination month, SQL Server returns the final day of that destination month. For example:

SELECT DATEADD(month, 1, CAST('2024-08-31' AS date)) AS NextMonth;

September has no thirty-first day, so the result is 2024-09-30. The same principle applies when adding one month to January 31: the result is the final day of February, which depends on whether the year is a leap year.

This behavior is convenient for many month-end reports, but it does not automatically define a recurring schedule. Repeatedly adding one month to the prior result can move an intended month-end or day-of-month schedule in ways the business did not expect. Define whether the requirement means a fixed day, the last day of every month, or a billing rule, then test transitions through February and shorter months.

If the number expression has a decimal fraction, DATEADD truncates the fraction instead of rounding it. The following expression adds one day, not two:

SELECT DATEADD(day, 1.9, CAST('2026-10-26' AS date)) AS TruncatedResult;

The result is 2026-10-27. Do not use fractional values to represent part of a datepart. Choose a smaller unit instead. For example, express one and one-half hours as 90 minutes rather than 1.5 hours. Explicit unit conversion is easier to review and does not depend on truncation.

DATEADD is commonly paired with a function that supplies the current date and time. In SQL Server, GETDATE() and CURRENT_TIMESTAMP return datetime, while SYSDATETIME() returns the higher-precision datetime2(7). SYSUTCDATETIME() supplies a UTC datetime2 value.

SELECT
    DATEADD(hour, 24, SYSDATETIME())    AS LocalDeadline,
    DATEADD(hour, 24, SYSUTCDATETIME()) AS UtcDeadline;

Choose local server time or UTC according to the application requirement. Adding an hour does not perform time-zone conversion and does not by itself resolve daylight-saving transitions. Systems that coordinate users across regions should store and interpret offsets or time-zone rules explicitly.

A sequence of offsets can turn one starting date into a forecast schedule. This self-contained recursive common table expression generates dates one through six months ahead:

WITH MonthOffsets AS
(
    SELECT 1 AS MonthsAhead

    UNION ALL

    SELECT MonthsAhead + 1
    FROM MonthOffsets
    WHERE MonthsAhead < 6
)
SELECT
    MonthsAhead,
    DATEADD(
        month,
        MonthsAhead,
        CAST(SYSDATETIME() AS date)
    ) AS FutureDate
FROM MonthOffsets
ORDER BY MonthsAhead;

The query defines its own sequence instead of referring to an unexplained seq table. For a larger or frequently used range, a permanent calendar or numbers table is usually a better design. Such a table can also store business-day flags, fiscal periods, holidays, and month-end attributes that DATEADD alone does not know.

In SQL Server 2025 (17.x), the number argument can use bigint. Earlier SQL Server versions limit that argument to the int range. This change supports much larger offsets, but it does not make every large result valid. The resulting value must still fit within the range of the date data type, and the chosen datepart must make sense for that type.

Code intended to run on multiple SQL Server versions should not assume bigint support. Confirm the target engine version and test the actual range that the application requires. Most business calculations need modest offsets, so an unusually large number should also be reviewed as a possible unit or data-quality error.

A DATEADD calculation can fail for two related reasons. On versions before SQL Server 2025, or where the older behavior applies, the number may exceed the range of int. Separately, even an accepted number may push the resulting date outside the range supported by the input data type. Adding centuries to a datetime value near its upper limit, for example, cannot produce a valid datetime.

Validate external offsets before they reach the query, use a temporal type with an appropriate range, and test boundary cases in a development environment. Do not suppress an overflow by blindly converting to another type. First determine whether the requested calculation is legitimate and whether the business rule chose the correct unit.

The concept of adding an interval to a date is common across database systems, but the syntax is not standardized around SQL Server's DATEADD form. PostgreSQL commonly adds an INTERVAL value, MySQL provides DATE_ADD, and Oracle supports date arithmetic and functions such as ADD_MONTHS. Those expressions are not interchangeable with T-SQL.

When a course, query, or application targets SQL Server, use the documented T-SQL dateparts and data types shown here. When moving logic to another product, translate the date arithmetic deliberately and retest month-end rules, implicit conversions, precision, and overflow behavior.

• Quoting the datepart: Keep month unquoted in DATEADD(month, 1, @Date).
• Treating weekday as business day: DATEADD(weekday, 1, @Date) does not skip weekends or holidays.
• Using ambiguous date strings: Prefer typed parameters, typed columns, or unambiguous ISO-style values with four-digit years.
• Expecting decimals to round: The number is truncated. Convert the requirement to a smaller whole-number unit.
• Ignoring the return type: A string literal returns datetime, while a typed input generally preserves its type.
• Assuming every month has the same day: Test month-end behavior whenever the source date can fall on the twenty-ninth, thirtieth, or thirty-first.
• Wrapping an indexed column unnecessarily: Calculate the boundary with DATEADD and compare the column to that boundary when possible.
• Assuming one vendor's syntax works everywhere: Translate and verify date arithmetic for the target database engine.

SQL Server's DATEADD(datepart, number, date) function shifts a temporal value by a selected unit. Use a positive number to move forward and a negative number to move backward. Keep the datepart as an unquoted token, choose an input type deliberately, and remember that decimal numbers are truncated.

Reliable use also requires attention to calendar and type rules. Missing days at the end of a target month resolve to that month's final day; the result must remain within the input type's range; and SQL Server 2025's bigint number support should not be assumed on earlier versions. With those rules understood, DATEADD becomes a practical tool for due dates, schedules, rolling windows, renewal calculations, and forecasts.