Definition and usage
The DATEADD () function adds or subtracts a specified time interval from a date.
Grammar
DATEADD (datepart,number, date)
The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative.
The datepart parameter can be the following value:
DatePart |
Abbreviations |
Years (year) |
YY, yyyy |
Quarter (Quarter) |
QQ, Q |
Monthly (month) |
MM, M |
Day of the Year (DayOfYear) |
Dy, y |
Days (Day) |
DD, D |
Week (week) |
WK, WW |
Week (weekday) |
DW, W |
Hours (hour) |
hh |
Minutes (minute) |
MI, n |
Seconds (second) |
SS, S |
Milliseconds (millisecond) |
Ms |
Subtlety (microsecond) |
MCs |
nanoseconds (nanosecond) |
Ns |
Instance
Suppose we have the following "Orders" table:
OrderId |
ProductName |
OrderDate |
1 |
' Computer ' |
2008-12-29 16:25:46.635 |
Now, we want to add 2 days to "OrderDate" so we can find the payment date.
We use the following SELECT statement:
SELECT OrderId,DATEADD(Day,2 as orderpaydate from Orders
Results:
OrderId |
orderpaydate |
1 |
2008-12-31 16:25:46.635 |
The SQL Server DATEADD () function adds or subtracts a specified time interval from a date