Re: Date Routines
From: Peter McKenzie <pemmck_at_ibm.net>
Date: 1997/12/23
Message-ID: <67neb7$s0j$1_at_marri.bs.wa.gov.au>#1/1
Date: 1997/12/23
Message-ID: <67neb7$s0j$1_at_marri.bs.wa.gov.au>#1/1
- Try this for starters
--
create or replace function public_holiday ( pi_date date ) return varchar2 as
--
l_return varchar2(5) := 'FALSE'; -- my old sqlplus doesn't like booleans !
--
cursor c_h ( cp_date date ) is select null is_a_holiday from holiday where holiday_date = cp_date ;
--
begin -- for r_h in c_h(pi_date) loop l_return := 'TRUE'; end loop; -- return(l_return); -- end;
- Then this
--
create or replace function bus_days ( pi_start_date date, pi_bus_days number ) return date as
--
l_test_date date := pi_start_date; l_bus_days number := pi_bus_days;
--
begin -- while true loop if to_char(l_test_date,'DY') not in ('SAT','SUN') then if public_holiday(l_test_date) = 'FALSE' then l_bus_days := l_bus_days -1; end if; end if; -- l_test_date := l_test_date + 1; -- if l_bus_days = 0 then exit; end if; -- end loop; -- return(l_test_date); -- end;
--
D&L wrote in message <349E9183.736D_at_inficad.com>... >computes the >resulting date based on excluding weekends and holidays (that latter >routine is actually the one we would use the most). > >Any ideas? > >--Dave