Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

SQL Server Help: Finding the difference between two dates in Transact-SQL

This can be achieved in SQL Server by using the DateDiff function. The function takes three arguments, a date part, plus two dates which can be either datetime or smalldatetime variables. The first argument, the date part, can be one of the following abbreviations:

yy, yyyy
Year
qq, q
Quarter
mm, m
Month
dy, y
DayOfYear
dd, d
Day
wk, ww
Week
hh
Hour
mi, n
Minute
ss
Second
ms
Millisecond

The date part does not need to be enclosed in quotes.

In the example below, the SQL statement is used to find orders where the ShippedDate column was more than 14 days after the value specified in the RequiredDate column:

SELECT * FROM Orders WHERE DATEDIFF(dd, RequiredDate, ShippedDate) > 14

Similarly, this SQL statement will return orders shipped two or more weeks after the required date:

SELECT * FROM Orders WHERE DATEDIFF(wk, RequiredDate, ShippedDate) >= 14

As well as using the DateDiff statement in WHERE clauses, it is also possible to return it in results set. This query shows the number of days by which orders were shipped after their required dates (the orders with the largest delay are returned first.

SELECT Orders.OrderID, DateDiff(dd, RequiredDate, ShippedDate) AS 'NumberOfDaysLate' FROM Orders
WHERE DateDiff(dd, RequiredDate, ShippedDate) > 0
ORDER BY DateDiff(dd, RequiredDate, ShippedDate) DESC

To determine the year, month, day, week or time from a date, the DatePart function can be used.

Useful Links

  • The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012