Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Date Time FunctionsRSS Feeds

DATEADD Function

Posted By: Sarita Patel     Category: SQL Server     Views: 10155

This article explains DATEADD function with different examples in sql server.

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. 

  
Share: 

 
 
 

Didn't find what you were looking for? Find more on DATEADD Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of DATEADD Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!