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



Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.