Solved

Working with dates

Posted on 1998-09-07
4
1,409 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now