Transact SQL Other Articles Software Reviews
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:
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
To determine the year, month, day, week or time from a date, the DatePart function can be used.