DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Date calculations

  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    To simplify the process, include Sat and Sun in the holiday table.

  5. #5
    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 ;
    p_business_days number := 0 ;
    Begin
    nextday := today ;
    while p_business_days <> 15
    loop
    select
    1 + nextday - today -
    ((TRUNC(nextday,'D') - TRUNC(today,'D'))/7)*2
    + DECODE(to_char(nextday,'D'),7,-1,0)
    + DECODE(to_char(today,'D'),1,-1,0) Business_Days
    into p_business_days
    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
  •  


Click Here to Expand Forum to Full Width