 Calculating Business Days on Oracle By: Quinn Wildman Abstract: A function you can use to calculate business days. Includes provisions for after hours calculations. The are quite a number of examples already on the web for calculating business days on Oracle. Here are some links to what I found: http://www.arrowsent.com/oratip/tip6.htm http://www.orsweb.com/memberarea/downloads/source/87.htm http://dco-proxima.dco.pima.edu/oracle/tfts/PRE00136.HTM 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; ``` Published on: 5/30/2001 12:00:00 AM Server Response from: ETNASC03

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.