Way to find the working days bet 2 dates

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

Hi,

Send me your Email address at
oracledba8@orasearch.net

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

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

http://dbasupport.com/oracle/faq/Detailed/324.shtml

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

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 */
countBusiness number := 0; /* counter for business days */

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
countBusiness := countBusiness + 1;
end if;

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

end loop;
return (countBusiness);
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

In PL/SQL you can use the logic:

select count( * ) days_business
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