DATEADD (T-SQL)

Returns a new datetime value based on adding an interval to the specified date.

Syntax

DATEADD(datepart, number, date)

Arguments

datepart

Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.

 

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

number

Is the value used to increment datepart.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. For information about specifying dates, see datetime and smalldatetime.

If you specify only the last two digits of the year, values that are less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values that are greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Return Types

Returns datetime, but smalldatetime if the date argument is smalldatetime.

Examples

This example prints a listing of a time frame for titles in the pubs database. This time frame represents the existing publication date plus 21 days.

USE pubs

GO

SELECT DATEADD(day, 21, pubdate) AS timeframe

FROM titles

GO

Here is the result set:

timeframe                  

---------------------------

Jul 3 1991 12:00AM         

Jun 30 1991 12:00AM        

Jul 21 1991 12:00AM        

Jul 13 1991 12:00AM        

Jun 30 1991 12:00AM        

Jul 9 1991 12:00AM         

Mar 14 1997  5:09PM        

Jul 21 1991 12:00AM        

Jul 3 1994 12:00AM         

Mar 14 1997  5:09PM        

Nov 11 1991 12:00AM        

Jul 6 1991 12:00AM         

Oct 26 1991 12:00AM        

Jul 3 1991 12:00AM         

Jul 3 1991 12:00AM         

Nov 11 1991 12:00AM        

Jul 3 1991 12:00AM         

Jul 3 1991 12:00AM         

(18 row(s) affected)

See Also