RSS Feed

DATEADD()


DATEADD()

Adds or subtracts a specified time interval from a specified date.

Syntax

DATEADD (datepart, number, date)

date is a valid date and number is the number of interval you want to add and DatePart is:

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

Example 1 of DATEADD()

select dateadd(Day, 1, Getdate()) as tomorrow

If the above query is ran on 7-july-2010 then the result will be:

2010-07-09 18:18:03.577

Example 2 of DATEADD()

below example decreases the date by 2 months

select dateadd(mm, -2, '07-08-2010')

2010-05-08 00:00:00.000