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
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.
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.
To simplify the process, include Sat and Sun in the holiday table.
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 ;
Click Here to Expand Forum to Full Width