[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Working with dates

Posted on 1998-09-07
Medium Priority
1,423 Views
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
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

LVL 3

Expert Comment

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.

0

LVL 3

Accepted Solution

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
end if;

end loop;

end;
/
0

LVL 3

Expert Comment

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;

0

Author Comment

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

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
###### Suggested Courses
Course of the Month12 days, 13 hours left to enroll

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

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