Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calculating the Amount of Business Hours between Two Dates

547979Nov 23 2006 — edited Nov 24 2006
Hi All!
I have just started working on a methodology on how to calculate the amount of business hours (8 hour day) between two different dates (mm/dd/yy hh:mm). I'm limited in that there is no calender table and I do not have the permission to create one. :(

Step One:
Calculate the date difference:
Close_Date - Open_Date

Step Two:
Calculate how many weeks between the dates:
TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7))

Step Three:
For any amount of weeks we multiply by 2 to get a weekend count:
TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7)) *2)

Step Four:
Subtract Step Three from Step One to get the amount of days passed which aren't a weekend:
(Wfm_Case.Close_Date - Wfm_Case.Open_Date) - (TRUNC(ABS(( Wfm_Case.Close_Date - Wfm_Case.Open_Date ) / 7)) *2)

From here I have a fractional result, which is just the days between the two dates minus any weekends.

This is where I get stuck :( I'm not sure on how to get the total business hours from this fractional result! Any help would be appreciated! I have been ripping my hair out about this one!

Thanks in advance!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2006
Added on Nov 23 2006
3 comments
636 views