Pages

Men

rh

7/30/2013

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 timeQueryResult

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


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


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

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

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

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

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

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

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

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

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

YEARSELECT 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

No comments :

Post a Comment