Solved

Working with dates

Posted on 1998-09-07
4
1,414 Views
Last Modified: 2011-10-03
Hello,
I have to figure out how many workdays are within a specified range.
I know that through dual I can get the TOTAL number of days between the dates but how do I eliminate the weekends?

If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years.

Thanks for your help.
0
Comment
Question by:sinner052397
  • 2
4 Comments
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081464
off the top of my head thought...

take the total number of days MOD by 7 to give you the number of weeks between the two dates, multiply this by five to give the number of working days - all you have to do then is work out how many working days are left in the remainder and add them on.  Of course this assumes every week day is a working day and ignore public holidays etc.

Vlad
0
 
LVL 3

Accepted Solution

by:
junfeb earned 100 total points
ID: 1081465
Here is a function  to calculate the number of business days between two dates -

create or replace function
num_Business_Days(start_date in date, end_date in date)
return number is
currdate        date := start_date;     /* holds the next date */
theDay          varchar2(10);/* day of the week for currdate */
countBusiness   number := 0;            /* counter for business days */
 
begin
 
/* start date must be earlier than end date */
if end_date - start_date <= 0 then
return (0);
end if;
 
loop
   /* go to the next day */
   currdate := to_date(currdate+1);
 
   /* finished if end_date is reached */
   exit when currdate = end_date;
 
   /* what day of the week is it? */
   select to_char(currdate,'fmDay') into theDay from dual;
 
   /* count it only if it is a weekday */
   if theDay <> 'Saturday' and theDay <> 'Sunday' then
    countBusiness := countBusiness + 1;
   end if;
 
end loop;
 
return (countBusiness);
 
end;
/
0
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081466
sinner, to quote yourself

"If possible, I would like to avoid a loop where it checks one day at a time because the ranges might be up to 12 years."

Not only does the answer you accepted do exactly what you did not want to do, it cannot handle holidays etc, I suggest that if you want an accurate answer you will have to come up with a more sophistcated solution.  I also suggest that, if you want a solution to your problem that meets your needs, you give people a chance to come up with a solution that meets your needs.  Of course if the proposed answer meets your needs than why did you bother asking for a solution that didn't loop through the dates?  to me it looks like you have accepted an answer which you already knew, or were you just looking for someone to do the coding for you?.

As an alternative you could adopt my suggestion and modify it a bit.

1) find out what day of the week the start and end dates fall on.
2) from the start date loop forward until the next instance of the day the end date falls on (eg if the start day is a wednesday and the end date a monday, count from the start date until the next monday) and count the number of working days in between (using a test as in the junfeb answer).  Take this new instance of the day your new start date
3) count the number of days between the new start date and the end date, multiply it by 5/7 to give the number of working days between.
4) add the two counts together to give the total number of days between the dates.

I suspect that if your dates are 12 years apart this might be faster than looping through all the days, you could also bundle it into a function and select it using your SQL statement e.g. SELECT days_between(start_date, end_date) from dual;

Vlad.
0
 

Author Comment

by:sinner052397
ID: 1081467
You are right.
I did accept an unsatisfactory answer but I have been going at this for days and so far every one has told me that I cannot avoid loops if I want to include holidays.
I already had a loop that was similar to the algorithm that was given. It's in PHP/Fi.
What it does is check if the day falls within the weekday.

If it's a weekday then it does a select into a table with dates of hollidays.

If it does not select any rows then $workingdays++

Using your solution then I would just have to do a select of holidays between the 2 dates and have to do a loop of those dates selected to check if they fell on a weekday or not.
But that loop would be minimal.


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question