DATEADD (TSQL Function)
Retuns a date after which a number of intervals of the specified type have been added.
Syntax
DATEADD ( datepart , count , date_expression )
Arguments
- datepart
- unit of the duration that will be added after date_expression.
See the list of valid values for datepart in the table below. - count
- number of dateparts that will be added after date_expression. It is of type
int
. - date_expression
- is the start date, of type
date
,time
,datetime
.
Return Types
Returns same type as date_expression.
Remarks
List of valid values for datepart:
datepart | Abbreviations | Remarks |
---|---|---|
year | yy , yyyy | |
quarter | qq , q | adds 3 months |
month | mm , m | |
dayofyear | dy , y | same as day |
day | dd , d | |
week | wk , ww | adds 7 days |
weekday | dw | same as day |
hour | hh | |
minute | mi , n | |
second | ss , s | |
millisecond | ms | |
microsecond | mcs | |
nanosecond | ns |
If months are added to a date, days past the last day of the month are clipped to the last day (see example).
Abbreviations can be used instead of the datepart full name. E.g. second
, ss
and s
are equivalent.
Examples
PRINT DATEADD(day, 125, '2015-06-15') -- add 125 days
PRINT DATEADD(d, 125, '2015-06-15') -- d is abbreviation for day
The result is:
2015-10-18 00:00:00
2015-10-18 00:00:00
Example with day clipping:
PRINT DATEADD(month, 1, '2015-03-30')
PRINT DATEADD(month, 1, '2015-03-31') -- in the result, day 31 is clipped to 30
The result is:
2015-04-30 00:00:00
2015-04-30 00:00:00