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

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 30 Jul 2003 15:22:27 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703F8F4FC@lnewton.leeds.lfs.co.uk>

Have you had a look at the NEXT_DAY function which returns the date of the next <chosen_day> from a gicven date :

select next_day(sysdate, 'MONDAY') from dual;

Gives the next Monday following today's date.

Of course, if today is Monday, you'll get a date back which is NEXT monday and not today.

etc.

Might help, might not .....

Cheers,
Norm.

-----Original Message-----

From: mike_murphy10_at_hotmail.com (Michael Murphy) [mailto:mike_murphy10_at_hotmail.com]
Posted At: Wednesday, July 30, 2003 2:07 PM Posted To: server
Conversation: Calculate the date + a number of working days. HOW? Subject: Calculate the date + a number of working days. HOW?

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
Received on Wed Jul 30 2003 - 09:22:27 CDT