dateadd

Description

Adds an interval to a specified date or time.

Syntax

dateadd(date_part, integer, {date | time | bigtime | datetime, | bigdatetime})

Parameters

date_part: is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see Transact-SQL Users Guide.

numeric: is an integer expression.

date expression: is an expression of type datetime, smalldatetime, bigdatetime, bigtime, date, time, or a character string in a datetime format.

Examples

Example 1

Adds one million microseconds to a bigtime:

declare @a bigtime
select @a = "14:20:00.010101"
select dateadd(us, 1000000, @a)

------------------------------
2:20:01.010101PM

Example 2

Adds 25 hours to a bigdatetime and the day will increment:

declare @a bigdatetime
select @a = "apr 12, 0001 14:20:00 "
select dateadd(hh, 25, @a)
------------------------------
Apr 13 0001   2:20PM

Example 3

Displays the new publication dates when the publication dates of all the books in the titles table slip by 21 days:

select newpubdate = dateadd(day, 21, pubdate) 
from titles

Example 4

Adds one day to a date:

declare @a date
select @a = "apr 12, 9999"
select dateadd(dd, 1, @a)
--------------------------
Apr 13 9999

Example 5

Subtracts five minutes to a time:

select dateadd(mi, -5, convert(time, "14:20:00"))
--------------------------
2:15PM

Example 6

Adds one day to a time and the time remains the same:

declare @a time
select @a = "14:20:00"
select dateadd(dd, 1, @a)
--------------------------
2:20PM

Example 7

Adds higher values resulting in the values rolling over to the next significant field, even though there are limits for each date_part, as with datetime values:

--Add 24 hours to a datetime
select dateadd(hh, 24, "4/1/1979")
-------------------------- 
Apr  2 1979 12:00AM 

--Add 24 hours to a date
select dateadd(hh, 24, "4/1/1979")
------------------------- 
Apr  2 1979

Usage

dateadd, a date function, adds an interval to a specified date. For information about dates, see Transact-SQL Users Guide.

dateadd takes three arguments: the date part, a number, and a date. The result is a datetime value equal to the date plus the number of date parts. If the last argument is a bigtime, and the datepart is a year, month, or day, the result is the original bigtime argument.

If the date argument is a smalldatetime value, the result is also a smalldatetime. You can use dateadd to add seconds or milliseconds to a smalldatetime, but such an addition is meaningful only if the result date returned by dateadd changes by at least one minute.

If a string is given as an argument in place of the chronological value the server interprets it as a datetime value regardless of its apparent precision. This default behavior may be changed by setting the configuration parameter builtin date strings or the set option builtin_date_strings. When these options are set the server will interpret strings given to chronological builtins as bigdatetimes. See the System Administration Guide for more information.

When a datepart of microseconds is given to this builtin string values will always be interpreted as bigdatetime.

Use the datetime datatype only for dates after January 1, 1753. datetime values must be enclosed in single or double quotes. Use the date datatype for dates from January 1, 0001 to 9999. date must be enclosed in single or double quotes.Use char, nchar, varchar, or nvarchar for earlier dates. Adaptive Server recognizes a wide variety of date formats. For more information, see “User-defined datatypes” and Transact-SQL Users Guide.

Adaptive Server automatically converts between character and datetime values when necessary (for example, when you compare a character value to a datetime value).
Using the date part weekday or dw with dateadd is not logical, and produces spurious results. Use day or dd instead.

**Table 2-4: date_part recognized abbreviations**
Date part	Abbreviation	Values
Year	yy	1753 – 9999 (datetime) 1900 – 2079 (smalldatetime) 0001 – 9999 (date)
Quarter	qq	1 – 4
Month	mm	1 – 12
Week	wk	1054
Day	dd	1 – 7
dayofyear	dy	1 – 366
Weekday	dw	1 – 7
Hour	hh	0 – 23
Minute	mi	0 – 59
Second	ss	0 – 59
millisecond	ms	0 – 999
microsecond	us	0 – 999999

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute dateadd.