Problem solve Get help with specific problems with your technologies, process and projects.

Difference between two dates using office hours

I need to calculate the difference between two dates and display the result as the number of weeks, days, hours 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

This was last published in May 2001

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.