DATEADD function is used to add interval to the specified date. It
returns a datetime value.
Syntax of DATEADD Function :
DATEADD (datepart ,number,date )
Datepart specifies part of the date to add a value.
Below table lists the dateparts
and abbreviations recognized by sql server.
DatePart | Abbreviation |
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. It takes integer
value. You can also provide negative value to subtract from a datepart. If you provide decimal value
then it discards the fractional part of the value.For example, if you specify day for datepart and 2.50 for number, date is incremented by 2.
Date is an expression that returns a datetime or smalldatetime value or
a character string in a date format.
It returns datetime if date argument is of
type datetime and smalldatetime if
the date argument is smalldatetime.
Examples of DATEADD function :
Example 1 : Using DATEADD function
in SELECT clause to add month in a date
SELECT ProductName, DATEADD(Month,2,RequiredDate)
AS NewRequiredDate
FROM Invoices
Output
ProductName NewRequiredDate
Alice Mutton 1998-05-16 00:00:00.000
Sasquatch Ale 1998-05-16 00:00:00.000
Jack's New England Clam Chowder 1998-04-16 00:00:00.000
Nord-Ost Matjeshering 1998-05-17 00:00:00.000
Raclette Courdavault 1998-05-17 00:00:00.000
Northwoods Cranberry Sauce 1998-04-18 00:00:00.000
Above example increments month of the required date by 2.
Example 2: Using DATEADD
function in SELECT clause to subtract month from a date
SELECT ProductName, DATEADD(Month,-2,RequiredDate)
AS NewRequiredDate
FROM Invoices
Output
ProductName NewRequiredDate
Alice Mutton 1998-01-16 00:00:00.000
Sasquatch Ale 1998-01-16 00:00:00.000
Jack's New England Clam Chowder 1998-02-16
00:00:00.000
Nord-Ost Matjeshering 1998-01-17 00:00:00.000
Raclette Courdavault 1998-01-17
00:00:00.000
Northwoods Cranberry Sauce 1998-02-18 00:00:00.000
Above example decrements month of the required date by 2.
Example 3 : Using DATEADD function in WHERE clause
SELECT ProductName, RequiredDate, OrderDate
FROM Invoices
WHERE RequiredDate = DATEADD(Month,1,OrderDate)
Output
ProductName RequiredDate OrderDate
Alice Mutton
1998-03-16 00:00:00.000 1998-02-16 00:00:00.000
Sasquatch Ale
1998-03-16 00:00:00.000 1998-02-16 00:00:00.000
Nord-Ost Matjeshering
1998-03-17 00:00:00.000 1998-02-17
00:00:00.000
Raclette Courdavault
1998-03-17 00:00:00.000 1998-02-17
00:00:00.000
Above example displays use of dateadd function in where
clause to compare required date and order date.