I need to calculate the difference between two dates and display the result as the number of weeks, days, hours...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
and minutes. This in itself is not a problem, but I want to use only office hours in the query, i.e., ignore weekends and do not calculate hours after 18:30 and before 08:00, public holidays excluded etc.
The best way to approach this problem is with the use of a Calendar table. Office hours are from 08:00 to 18:30? Okay, that's 10.5 hours.
Caldate DayOfWeek OfficeHours Holiday 2001-05-24 Thu 10.5 2001-05-25 Fri 10.5 2001-05-26 Sat 0 2001-05-27 Sun 0 2001-05-28 Mon 0 Memorial Day 2001-05-29 Tue 10.5
To find the office hours between two dates, you use a query like this --
select sum(OfficeHours) from Calendar where Caldate >= #startdate# and Caldate < #enddate#
where #startdate# and #enddate# are your variables.
The main task then will be updating the Calendar table annually -- probably by hand, i.e. by preparing the data in, say, Excel, and then loading it into the table with a database utility.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.