?
Solved

Working with dates

Posted on 1998-09-07
4
Medium Priority
?
1,419 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 300 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

752 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