Difference between two dates using office hours

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.