DATEADD Function

Description

Adds a specified time value to a date or time value and returns a modified date or time value.

DATEADD (datepart , number , date )

DATEADD returns a valid datetime expression. It's the numerical expression that datepart argument represents, added to the date argument.

Syntax

DATEADD ( datepart , number , date )

Arguments

Parameter Description Data Type
datepart Specifies the part of the date to which the number is added. This parameter can be one of the following values:
  • year (yy, yyyy)
  • quarter (qq, q)
  • month (mm, m)
  • dayofyear (dy, y)
  • day (dd, d)
  • week (wk, ww)
  • hour (hh)
  • minute (mi, n)
  • second (ss, s)
  • millisecond (ms)
  • microsecond (mcs)
  • nanosecond (ns)
 varchar or int
number The integer value that will be added to the specified datepart of the date. Can be a positive or negative integer. int
date An expression that evaluates to a datetime2, datetime, datetimeoffset, smalldatetime, date, time, or time(n) value. datetime2, datetime, datetimeoffset, smalldatetime, date, time, time(n)

Return Type

Returns the modified date or time value. The return type depends on the input date argument.

Examples

Adding days to a date

Add 10 days to the current date:

SELECT DATEADD(day, 10, GETDATE());

Subtracting months from a date

Subtract 3 months from November 15, 2023:

SELECT DATEADD(month, -3, '2023-11-15');

Adding years and checking leap year

Add 1 year to February 29, 2024:

SELECT DATEADD(year, 1, '2024-02-29');

This will return '2025-02-28'.

Adding time components

Add 2 hours and 30 minutes to a specific time:

SELECT DATEADD(minute, 30, DATEADD(hour, 2, '2023-10-27 14:00:00'));

Remarks

When you specify a datepart and a number, DATEADD will increment or decrement the date. The function is not limited to adding or subtracting within the same date part. For example, if you add 1 month to January 31, 2023, the result will be February 28, 2023, because February only has 28 days.

Note: The abbreviations for datepart are not case-sensitive.

For compatibility with earlier versions of SQL Server, DATEADD supports only datetime and smalldatetime data types when the datepart is year, month, or day.

See Also