Calculating Business Days on Oracle

Abstract: A function you can use to calculate business days. Includes provisions for after hours calculations.

There are quite a number of examples already on the web for calculating business days on Oracle.
However, none of these did exactly what I needed for a job I was doing. What's different about my environment is the need to consider multiple geographic regions and to consider work hours and that different regions might have different work hours. Here are the deciding factors for how this function decides what to with timestamp after hours (both weekends and during the week)

• If the start date is after the end of the work day, and before midnight, it is advanced to the beginning of next work day.
• If the start date is before the beginning of the day (and therefore after midnight), it is advanced to the beginning of the current work day.
• If the start date is on a Saturday, it is advanced to the beginning of work day on the following Monday.
• If the start date is on a Sunday, it is advanced to the beginning of work day on the following Monday.
• If the end date is after the end of the work day, and before midnight, it moved to the end of the day.
• If the start date is before the beginning of the day (and therefore after midnight), it is advanced to the beginning of the current work day.
• If the start date is on a Friday or Saturday it is moved to end of the previous Friday.

This function does not consider holidays, but could be modified to do so if you had a holiday table.

CREATE OR REPLACE FUNCTION bdays(start_date IN DATE, end_date IN DATE, region IN CHAR) 
RETURN NUMBER 
IS
  retval NUMBER(15,7);
  new_start_date date;
  new_end_date date;
  bdaystart number(15,15);
  bdayend number(15,15);
BEGIN
new_start_date := start_date;
new_end_date := end_date;
-- set defaults for business day start and end. Can be overridden per region
bdaystart := 7/24;
bdayend := 17/24;
if region='Europe' then
  new_start_date := new_start_date + 9/24;
  new_end_date := new_end_date + 9/24;
  bdaystart := 9/24;
  bdayend := 18.5/24;
end if;
if region='Asia-Pac' then
  new_start_date := new_start_date + 15/24;
  new_end_date := new_end_date + 15/24;
end if;
--Start After end of day, make start be start of next day
if new_start_date-trunc(new_start_date)>bdayend then
  new_start_date := TRUNC(new_start_date+1)+bdaystart;
end if;
--Start before start of day, make start be start of same day
if new_start_date-trunc(new_start_date) < bdaystart then
  new_start_date := TRUNC(new_start_date) + bdaystart;
end if;
--Start Saturday, make start be Monday start of day
if to_char(new_start_date,'D')=7 THEN
  new_start_date := TRUNC(new_start_date+2)+bdaystart;  
END IF;
--Start Sunday, make start be Monday start of day
if to_char(new_start_date,'D')=1 THEN
  new_start_date := TRUNC(new_start_date+1)+bdaystart;
END IF;
-- end after end of day, make end be end of day same day
if new_end_date-trunc(new_end_date) > bdayend then
  new_end_date := trunc(new_end_date) + bdayend;
end if;
-- end before start of day, make end be start of day the same day
if new_end_date-trunc(new_end_date) < bdaystart then
  new_end_date := trunc(new_end_date) + bdaystart;
end if;
--end on Saturday, make it be the end of the day on Friday
if to_char(new_end_date,'D')=7 then
  new_end_date := trunc(new_end_date-1) + bdayend;
end if;
--end on Sunday, make it be the end of the day on Friday
if to_char(new_end_date,'D')=1 then
  new_end_date := trunc(new_end_date-2) + bdayend;
end if;
--factor out weekend days
retval := new_end_date - new_start_date -
((TRUNC(new_end_date,'D') - TRUNC(new_start_date,'D'))/7)*2;
-- if holidays were to be calculated, the calculation would go here
-- if end is during nonbusiness hours, difference could be negative
if retval < 0 then 
  retval := 0;
end if;
RETURN(retval);
END;