Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Calculate the date + a number of working days. HOW?

Re: Calculate the date + a number of working days. HOW?

From: Jim Kennedy <kennedy-down_with_spammers_at_no_spam.comcast.net>
Date: Wed, 30 Jul 2003 14:19:49 GMT
Message-ID: <9uQVa.19665$o%2.11305@sccrnsc02>


"Michael Murphy" <mike_murphy10_at_hotmail.com> wrote in message news:6cfe2956.0307300507.36289e19_at_posting.google.com...
> I am disgraced by my way of calculating a date + a number of working
> days (involving a loop and running very slowly). I will post my 'code'
> if I really have to but don't want to show it at the moment because.
>
> Parts of my code is below. All I want to do is take a date and add a
> number of working days to it. Working days are days that are not
> Saturday or Sunday and not in the table holidays. I can find lots of
> examples calculating the number of working days between two dates but
> this isn't what I want.
>
> Any ideas on the code for the function "daysplus" I have put below.
>
> Thank you
> Michael
>
>
> create table holidays(holiday date);
>
> --wednesday
> insert into holidays values ('01-jan-2003');
>
> --tursday
> insert into holidays values ('02-jan-2003');
>
> --friday
> insert into holidays values ('03-jan-2003');
>
> --tuesday
> insert into holidays values ('07-jan-2003');
>
> create function daysplus(startdate in date,daystoadd number) as date
> is
> begin
> --some stuff in here to add "daystoadd" working days (ie days that
> are not Sat
> --or Sun or in the table "holidays" to startdate
> end;
>
>
> so that:
>
> select daysplus(to_date('30-dec-2002'),1) from dual;
> -- returns 06-jan-2003 --monday
>
> select daysplus(to_date('01-jan-2003'),1) from dual;
> -- returns 06-jan-2003 --monday
>
> select daysplus(to_date('02-jan-2003'),1) from dual;
> -- returns 06-jan-2003 --monday
> .
> .
> . etc
> .
> .
> select daysplus(to_date('06-jan-2003'),1) from dual;
> -- returns 08-jan-2003 --wednesday
>
> select daysplus(to_date('07-jan-2003'),1) from dual;
> -- returns 08-jan-2003 --wednesday
>
> select daysplus(to_date('08-jan-2003'),1) from dual;
> -- returns 09-jan-2003 --thursday
>
> select daysplus(to_date('08-jan-2003'),2) from dual;
> -- returns 10-jan-2003 --friday
>
> select daysplus(to_date('08-jan-2003'),3) from dual;
> -- returns 13-jan-2003 --monday

Go to asktom.oracle.com. I remember seeing this type of thing there. Jim Received on Wed Jul 30 2003 - 09:19:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US