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.

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.