Logo 
Search:

SQL Server Articles

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

DATEDIFF Function

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

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

DATEDIFF function is used to get difference between dates specified.

 

Syntax of DATEDIFF Function :

DATEDIFF ( datepart ,startdate ,enddate )


Datepart is the part of the date to calculate the difference.

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

startdate is subtracted from enddate.

Startdate is the beginning date for the calculation. startdate is an expression that returns a datetime or smalldatetime value or a character string in a date format.

Enddate is the ending date for the calculation. enddate is an expression that returns a datetime orsmalldatetime value, or a character string in a date format.

It returns an integer value.

 

Examples of DATEDIFF Function :

Example 1 : Using DATEDIFF function in SELECT clause

SELECT ShipName, DATEDIFF(DAY,OrderDate, ShippedDate) AS 'Days'

FROM    Orders  

Output

ShipName                             Days

Vins et alcools Chevalier       12

Toms Spezialitäten                  5

Hanari Carnes                          4

Victuailles en stock                 7

Suprêmes délices                     2

Hanari Carnes                          6


Above example displays date difference of shipped date and order date of order table.

 

 

Example 2 : Using DATEDIFF function in WHERE clause

SELECT  ShipName, DATEDIFF(WEEK,OrderDate, ShippedDate) AS 'Weeks'

FROM     Orders  

WHERE  DATEDIFF(WEEK,OrderDate, ShippedDate) < 10

Output

ShipName                             Days

Toms Spezialitäten                  5

Hanari Carnes                          4

Victuailles en stock                 7

Suprêmes délices                     2

Hanari Carnes                          6


Above example displays orders where shippeddate and orderdate difference is less than 10 weeks.

 

 

  
Share: 

 
 
 

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

Sarita Patel
Sarita Patel author of DATEDIFF 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!