SQL Server DATEADD() Function

The DATEADD() function is a function in SQL Server used to add or subtract a specified time interval from a date. It is commonly used in queries to calculate dates and can add time intervals such as years, months, days, hours, and minutes.

Syntax

DATEADD(datepart, number, date)

Parameters:

• datepart: The date or time part to add. Use a supported literal such as year, month, day, hour, or minute; the datepart cannot be supplied as a variable.
  • year: year
  • quarter: quarter
  • month: month
  • dayofyear: day of the year
  • day: day
  • week: week
  • weekday: weekday
  • hour: hour
  • minute: minute
  • second: second
  • millisecond: millisecond
  • microsecond: microsecond
  • nanosecond: nanosecond
• number: represents the number of the time interval to be added
• date: represents the date to which the time interval is to be added

In SQL Server 2025 (17.x), number can be a bigint; older SQL Server versions limit it to the int range. If number has a fractional part, DATEADD() truncates it rather than rounding it. See Microsoft’s SQL Server 2025 notes and DATEADD reference.

The microsecond and nanosecond date parts require a type that supports fractional seconds. They are not supported for date, smalldatetime, or datetime; use time, datetime2, or datetimeoffset. These types have at most seven fractional digits. For DATEADD(nanosecond, n, value), an n from 1 through 49 rounds down to no change, while 50 through 99 rounds up to a 100-nanosecond increment. For example:

DECLARE @value datetime2(7) = '2024-01-01T13:10:10.1111111';

SELECT DATEADD(nanosecond, 49, @value) AS rounds_down,
       DATEADD(nanosecond, 50, @value) AS rounds_up;

The results are 2024-01-01 13:10:10.1111111 and 2024-01-01 13:10:10.1111112, respectively. See Microsoft’s DATEADD fractional-seconds examples.

Usage

The DATEADD() function is very useful for date calculations in queries, for example:

• Calculate a date a certain number of days from another date
• Calculate a date a certain number of months from another date
• Calculate a date a certain number of years from another date
• Calculate a date a certain number of hours from another date
• Calculate a date a certain number of minutes from another date

Examples

Example 1: Calculate a date a certain number of days from another date

Suppose we want to calculate the date 7 days after March 11, 2023. We can use the following SQL statement:

SELECT DATEADD(day, 7, '2023-03-11') AS Result;

The result of the query is:

Example 2: Calculate a date a certain number of months from another date

Suppose we want to calculate the date 3 months after March 11, 2023. We can use the following SQL statement:

SELECT DATEADD(month, 3, '2023-03-11') AS Result;

The result of the query is:

Add a month when the target month is shorter

If the original day does not exist in the target month, DATEADD(month, ...) returns the last day of that month. For example, adding one month to August 31 returns September 30:

SELECT DATEADD(month, 1, '2024-08-31') AS Result;

Result:

See Microsoft’s DATEADD documentation for the supported date parts, return types, and overflow behavior.