Chapter 2: Transact-SQL Functions

dateadd

Description

Returns the date produced by adding or subtracting a given number of years, quarters, hours, or other date parts to the specified date.

Syntax

dateadd(date_part, integer, date expression)

Parameters

date_part: is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.

numeric: is an integer expression.

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

Examples

Example 1

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 2

Add one day to a date:

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

Example 3

Subtracts five minutes to a time:

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

Example 4

Add 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 5

Although there are limits for each date_part, as with datetime values, you can add higher values resulting in the values rolling over to the next significant field:

--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 more information about date functions, see “Date functions”.
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 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.
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 “Datatype conversion functions”.

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-6: 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

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute dateadd.