# Thread: Way to find the working days bet 2 dates

1. Senior Member
Join Date
Jan 2001
Posts
318
Is their any easy( faster) way to find the number of business days between the 2 dates in PL/SQl block- procedure I am using Oracle 8.0.6 ?
I ususally do:

/*if startdate is sat or sun get the coming Mon as the start date*/

If to_char(dtTempStartdate1,'fmDay') ='Saturday'
or to_char(dtTempStartdate1,'fmDay') ='Sunday' then
Select Next_Day(dtTempStartdate1, 'Monday') into dtTempStartdate1 from dual;
end if;

/*If end date is Sat or Sun subtract 1 till you get to Fri*/

If to_char(dtTempEnddate1,'fmDay') ='Saturday'
or to_char(dtTempEnddate1,'fmDay') ='Sunday' then
Select Next_Day(dtTempEnddate1, 'Friday')-7 into dtTempEnddate1 from dual;
end if;

inDateCounter1:= dtTempEnddate1 - dtTempStartdate1 +1
- (Next_Day(dtTempEnddate1, 'Sunday')
- Next_Day(dtTempStartdate1, 'Sunday') )/7
- (Next_Day(dtTempEnddate1, 'Saturday')
- Next_Day(dtTempStartdate1, 'Saturday') )/7;

----------------------------------------
OR
-----------------------------------
While To_Number(TO_Char(dtTempStartdate2,'D')) =1
or To_Number(TO_Char(dtTempStartdate2,'D')) =7
Loop
dtTempStartdate2 :=dtTempStartdate2 +1;
End loop;
/*If end date is Sat or Sun subtract 1 till you get to Fri*/
While To_Number(TO_Char(dtTempEnddate2,'D')) =1
or To_Number(TO_Char(dtTempEnddate2,'D')) =7
Loop
dtTempEnddate2 := dtTempEnddate2-1;
End loop;

inDateCounter1:= dtTempEnddate1 - dtTempStartdate1 +1
- (Next_Day(dtTempEnddate1, 'Sunday') ...same as above....

Thanks
Sonali

2. Member
Join Date
Feb 2001
Posts
184
Hi,

oracledba8@orasearch.net

I'll send you a Procedure that might be helpfull.
Thanks

3. Create a function with indate and outdate variables and call that function in the stored procedure. See the following link.

[url]http://dbasupport.com/oracle/faq/Detailed/324.shtml[/url]

4. Junior Member
Join Date
Mar 2001
Posts
17
Hi

Make your logic in stored procedure and try to use INTERVAL function like...

SELECT CURRENT_DATE - INTERVAL '8' MONTH FROM DUAL;

Hope it will help u.

Kancha

5. Senior Member
Join Date
Jan 2001
Posts
318
Kancha:
I did not understand your query. Can you elaborate it please ?

Reddy: Sorry I should have explained what I want to do before.

I want to calculate number of business days so I don't want to include Saturdays and Sundays.
Also for calculating number of business days I want to use Monday as start date(if the start date entered is sat or sun)
and use Friday as the end date(if the end date entered is sat or sun), for any other day use that date as start and end date.

I have also written function to do this:

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 */

begin

loop

/* finished if end_date is reached */
exit when currdate = end_date+1;

/* 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;

/* go to the next day */
currdate := to_date(currdate+1);

end loop;
end;
/

But I am not sure if this is efficient way of doing it because it loops through each day between start and end date.

thanks
Sonali

6. Member
Join Date
Oct 2000
Posts
123
In PL/SQL you can use the logic:

from (
-------select rownum-1 r
-------from all_objects
-------where r <= date1 - date2 )
where to_char( date2 + r - 1, 'DY' ) not in ( 'SAT', 'SUN' );

Note: date1 is second date while date2 is first date.

Make modification for i'm not tested it(of my hat), but it works for me before.

Take care

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•