# Date calculations

• 10-10-2000, 12:33 PM
rblaylock
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
completion date.

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
for me?

TIA,

Ron Blaylock
rblaylock@mail.arc.nasa.gov
• 10-10-2000, 01:06 PM
tamilselvan
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 if
End do while

The new STDATE will be the ending date for the work order.

• 10-10-2000, 02:31 PM
rblaylock
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.

Best regards,

Ron B.
• 10-10-2000, 04:13 PM
tamilselvan
To simplify the process, include Sat and Sun in the holiday table.
• 10-10-2000, 08:48 PM
Victoria
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 ) "

Declare
today date := sysdate ;
nextday date ;
Begin
nextday := today ;