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

Source Collected from mssqltips.com