Skip to main content

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;
Labels:

Comments

Post a Comment

Popular posts from this blog

Expose your Local Development Environment to the outside world with Ngrok

When we develop on localhost, we usually use some kind of simple HTTP server like node, our Oracle database,APIs, webhooks, Callback Urls or whatever. This is all good and we are all pretty happy about that. We have access to our app using our fancy  http://localhost  url. We are happy, but  alone . What if you would like to share your app to a colleague that is not on the same network as yours ? What if you need to check your app on an SSL connection? What if you wanted an external system to pass you a process invoked by a method ? ngrok to the rescue Ngrok   is a simple “free” service that can help you with that. Here’s some of the features that it provides: Expose your locally hosted app/website to the outside world by providing you a  http(s)://{something}.ngrok.io  url. Allows you to have an SSL connection to your localhost environment. Inspect/replay the requests made to your local environment Custom subdomain (required a premium accou...
1 comment
Read more

Disabling of Password Expiration in Oracle Apex(Internal Workspace Admin)

This post was inspired by a  question  on the OTN APEX forum, which contains how to  reset set the password of the  Oracle Internal Workspace Admin    and  Set the account never to expire  The first bullet has so many blogs  talk about how to reset the password of the Internal Workspace. However, i am more intrigued with the second . To start of with It is not advisable to never expire accounts since its rudimental for user to always renew their accounts  prior to expiration. The default expiration of an account is mostly 180 days so hey whats the point going to do this again after 180 days?? . There are two methods that can be used to achieve this  Generic Never expiration of all User accounts (This should never be practiced in a production Environment All database users are assigned to something called a PROFILE . The profile controls two aspects of the users database acce...
Post a Comment
Read more

How does one add a day/hour/minute/second to a date value?

DATE is the datatype that we are all familiar  with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future.  The problem with the DATE datatype is its' granularity  when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date: SQL> SELECT  TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS')  "hiredate"   FROM employees; hiredate ------------------- 17.12.1980:00:00:00 20.02.1981:00:00:00 The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours...
5 comments
Read more