Home » Developer & Programmer » Forms » how to retrieve the working days ?
how to retrieve the working days ? [message #79926] Fri, 26 July 2002 04:18 Go to next message
kat
Messages: 15
Registered: February 2000
Junior Member
hi everyone!

for an application I need to know the number of workdays for the months (less the statutory holidays and weekends of course). do you have any suggestions how to solve this problem programmatically?

best regards ,
kat
Re: how to retrieve the working days ? [message #79934 is a reply to message #79926] Fri, 26 July 2002 07:42 Go to previous messageGo to next message
Bob H.
Messages: 3
Registered: July 2002
Junior Member
Something like this could be used to loop from the beginning of a month through the end and exclude the days that you do not wish to count.

declare
dweek varchar2(1);
counter number := 1;
date_calc date;
nbr_days number := 0;
begin
-- create a date variable that indicates the first day of the desired
-- month, however that is obtained, I substituted 07/01/2002
.
.
date_calc := to_date('07/01/2002','mm/dd/yyyy')
--<determine the number of days in the month>
-- if substr(to_char(date_calc,'mm/dd/yyyy'),1,2) in ('01','03','05','07','08','10','12) then
-- nbr_days := 31
.
.
-- Loop through until the number of days is used up
WHILE counter <= nbr_days LOOP
select to_char(date_calc,'d')
into dweek
from dual;
if dweek in ('2','3','4','5','6') then
-- increment the counter if day of week is
-- Monday through Friday
-- <Statuatory Holiday logic could wrap the counter increment>
counter := counter + 1;
end if;
-- add 1 to the date_calc
date_calc := to_date(to_char(to_date(to_number(to_char(date_calc,'j')) + 1,'J'),'mm/dd/yyyy'),'mm/dd/yyyy');
END LOOP;
end;
Re: how to retrieve the working days ? [message #79947 is a reply to message #79926] Sat, 27 July 2002 20:40 Go to previous message
Subhash
Messages: 105
Registered: October 2001
Senior Member
Try this.
Assumes that Saturday and Friday are ur holidays

select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('&L') - to_date('&F')+1 )
where to_char( to_date('&F')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )

L - Last Day of the Month
F - First Day of the Month
Previous Topic: How to create a dialogbox like a openfile dialog?
Next Topic: D2k questions
Goto Forum:
  


Current Time: Tue Nov 30 12:43:51 CST 2021