I am not a developer, I am a DBA but this can probably best be answered
by a developer.
I am attempting to set up a report that does a date calculation. For example,
a work order that needs to have 15 working days added to it for a required
Is there a way to add, for example, 15 working days to a start date to obtain
a completion date? Does anyone have any code that would provide an example
10-10-2000, 01:06 PM
I did it my previous project. I write the logic. It is better you develop your skill in writing PL/SQL procedure and function. Sooner or later it will help you. You do not need to depend upon others.
You need a hoilday table in which all hoildays are stored.
Set the starting date to STDATE
Set a counter to 15 (say Kount=15)
Do while kount > 0
STDATE = STDATE+1
if STDATE is not found in hoilday table (you need to write SQL)
KOUNT = KOUNT - 1
End do while
The new STDATE will be the ending date for the work order.
10-10-2000, 02:31 PM
Thanks for the info. That is what I figured but I'm having troubles getting the
date to convert in and out for my calculations. I also need to eliminate Saturday
and Sunday as well as holidays. I know sql fairly well but I am not using Oracle
so much anymore as I changed jobs and spend most of my time in Sybase
and on the mainframe.
I'll give your idea a shot and see if I can also calculate and remove weekends.
10-10-2000, 04:13 PM
To simplify the process, include Sat and Sun in the holiday table.
10-10-2000, 08:48 PM
This code will give you the 15th Working day from Today. Change 15 to something else If you please. To implement the holidays, just add the where clause
" where nextday not in ( select holiday from holiday_table ) "
today date := sysdate ;
nextday date ;
p_business_days number := 0 ;
nextday := today ;
while p_business_days <> 15
1 + nextday - today -
((TRUNC(nextday,'D') - TRUNC(today,'D'))/7)*2
+ DECODE(to_char(today,'D'),1,-1,0) Business_Days
from dual ;
nextday := nextday + 1 ;