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