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