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 |
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
Next Steps
- Check out these related resources:
- Read up on the DATEDIFF function
- Find out more about Using Date and Time Data
- Read about other Date and Time Functions
- Get the AdventureWorks 2008R2 sample databases
Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.
- MSSQLTips Awards: Trendsetter (25+ tips)
