Saturday, November 05, 2011

SQL Server DateDiff Example

Problem

At the moment I have a project where I have to retrieve SQL Server data based on the difference between two dates. I read the tip on the DATEADD function and wanted to know about other functions available for date/time manipulation. Can you point me in the right direction?  Check out this tip to learn more about the SQL Server DateAdd function.

Solution

The most common function I use for date/time manipulation is the DATEDIFF function. Instead of adding or subtracting units of time from a specified date/time value, the DATEDIFF function retrieves the number of units of time between a start and end time. The DATEDIFF function can also be used in a WHERE clause as well as ORDER BY and HAVING clauses. The units of time available for the DATEDIFF are the same as those for the DATEADD function.

Examples of Using the SQL Server DATEADD Function

Below are examples of using the DATEDIFF function:

Unit of time Query Result

NANOSECOND SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') 4321300


MICROSECOND SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') 4321


MILLISECOND SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548') 544

SECOND SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23') 53

MINUTE SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30') -48

HOUR SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30') 2

WEEK SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00') 12

DAY SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00') -52

DAYOFYEAR SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00') 213

MONTH SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00') -10

QUARTER SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00') 2

YEAR SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00') 5

One practical example of using the DATEDIFF function in SQL Server is in a WHERE clause by selecting all employees in the AdventureWorks2008R2 database whose date of hire was in March 2003. The application passes in two parameters: a string representing the number of the desired month (i.e. January=1, February=2, etc.) and the string representing the desired year. Then we use the DATEDIFF function with the MONTH unit of time:
 
DECLARE @Month VARCHAR(2), @Year CHAR(4)
SELECT @Month = '3', @Year = '2003'
DECLARE @Date DATE = @Month + '/1/' + @Year
SELECT LoginID, OrganizationNode, OrganizationLevel, JobTitle, BirthDate,
MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours,
SickLeaveHours, CurrentFlag, rowguid, ModifiedDate
FROM AdventureWorks2008R2.HumanResources.Employee
WHERE DATEDIFF(MONTH,@Date,HireDate) = 0;
GO

Author: Tim Cullen