Date calculations

1. Junior Member
Join Date
Feb 2000
Location
NASA Ames Research Center, Moffett Field, CA, USA
Posts
2
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

2. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
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.

3. Junior Member
Join Date
Feb 2000
Location
NASA Ames Research Center, Moffett Field, CA, USA
Posts
2
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.

4. Moderator
Join Date
May 2000
Location
ATLANTA, GA, USA
Posts
3,135
To simplify the process, include Sat and Sun in the holiday table.

5. Junior Member
Join Date
May 2000
Posts
58
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 ;
loop
select
1 + nextday - today -
((TRUNC(nextday,'D') - TRUNC(today,'D'))/7)*2
+ DECODE(to_char(nextday,'D'),7,-1,0)
from dual ;
nextday := nextday + 1 ;
end loop;
dbms_output.put_line(nextday) ;
end ;

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•