SQL Server DATEADD() Function
In SQL Server, the DATEADD() function adds a number to a datepart and returns the modified datetime value.
Syntax:
DATEADD(datepart, number, date)
Parameters
datepart: The specific part of the specified date parameter to which the DATEADD() function will add a number.
The following table lists all valid datepart values:
| Datepart | Abbreviation |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| day of year | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Note: dayofyear, day, and weekday return the same value.
number: An integer that is to be added to the specified datepart. It can be an expression that returns an integer. The number will be truncated if it has a decimal fraction.
Note: DATEADD() function returns an error if the number argument exceeds the range of int.
date: A datetime value or an expression that resolves to a datetime value. It can be a string expression, a table column, or a variable. A string literal value must resolve to type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME
Return Value
The return type depends on the argument supplied for the date parameter.
If the value of the date is a string literal date value, then DATEADD() returns a datetime value. If any other valid data type is supplied for the date column, then the same data type is returned from DATEADD() function.
Add Days in DateTime
The following example adds 1 day to the specified date '12/31/2021' (31st December 2021).
SELECT DATEADD(day,1,'12/31/2021') AS Result
In the above example, 'day' is the datepart that specifies that a number should be added to a day part of the specified date '12/31/2021'. The second parameter is a number that should be added, and the third parameter is a date where the specified parameter should be added.
Add Months in DateTime
In this example, for the month datepart, 2 is added to date '12/31/2021'. As you can see in the result, two months is added to the input date and the DATEADD() function returns 28th February 2022, which is the last day of the returned date.
SELECT DATEADD(month, 2, '12/31/2021') AS Result
You can specify negative number to be added in the date which will deduct a number from the specified datetime value.
SELECT DATEADD(month, -2, '12/31/2021') AS Result
Use DATEADD() with Columns
The following DATEADD() function is used with the HireDate column of the Employee table to return the JoinDate.
Two weeks is added to the HireDate column to return JoinDate.
SELECT EmployeeID, FirstName, HireDate, DATEADD(wk, 2, HireDate) AS JoinDate
FROM Employee
Make sure the specified date is in the correct format; otherwise it will raise an error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
SELECT DATEADD(month, -2, '12/31/2021 123') AS Result
