Way to find the working days bet 2 dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Way to find the working days bet 2 dates

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    Is their any easy( faster) way to find the number of business days between the 2 dates in PL/SQl block- procedure I am using Oracle 8.0.6 ?
    I ususally do:

    /*if startdate is sat or sun get the coming Mon as the start date*/

    If to_char(dtTempStartdate1,'fmDay') ='Saturday'
    or to_char(dtTempStartdate1,'fmDay') ='Sunday' then
    Select Next_Day(dtTempStartdate1, 'Monday') into dtTempStartdate1 from dual;
    end if;

    /*If end date is Sat or Sun subtract 1 till you get to Fri*/


    If to_char(dtTempEnddate1,'fmDay') ='Saturday'
    or to_char(dtTempEnddate1,'fmDay') ='Sunday' then
    Select Next_Day(dtTempEnddate1, 'Friday')-7 into dtTempEnddate1 from dual;
    end if;

    inDateCounter1:= dtTempEnddate1 - dtTempStartdate1 +1
    - (Next_Day(dtTempEnddate1, 'Sunday')
    - Next_Day(dtTempStartdate1, 'Sunday') )/7
    - (Next_Day(dtTempEnddate1, 'Saturday')
    - Next_Day(dtTempStartdate1, 'Saturday') )/7;

    ----------------------------------------
    OR
    -----------------------------------
    While To_Number(TO_Char(dtTempStartdate2,'D')) =1
    or To_Number(TO_Char(dtTempStartdate2,'D')) =7
    Loop
    dtTempStartdate2 :=dtTempStartdate2 +1;
    End loop;
    /*If end date is Sat or Sun subtract 1 till you get to Fri*/
    While To_Number(TO_Char(dtTempEnddate2,'D')) =1
    or To_Number(TO_Char(dtTempEnddate2,'D')) =7
    Loop
    dtTempEnddate2 := dtTempEnddate2-1;
    End loop;

    inDateCounter1:= dtTempEnddate1 - dtTempStartdate1 +1
    - (Next_Day(dtTempEnddate1, 'Sunday') ...same as above....



    Thanks
    Sonali

    Sonali

  2. #2
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Send me your Email address at
    oracledba8@orasearch.net

    I'll send you a Procedure that might be helpfull.
    Thanks

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Create a function with indate and outdate variables and call that function in the stored procedure. See the following link.

    [url]http://dbasupport.com/oracle/faq/Detailed/324.shtml[/url]
    Reddy,Sam

  4. #4
    Join Date
    Mar 2001
    Posts
    17

    Smile

    Hi

    Make your logic in stored procedure and try to use INTERVAL function like...

    SELECT CURRENT_DATE - INTERVAL '8' MONTH FROM DUAL;

    Hope it will help u.

    Kancha

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    Kancha:
    I did not understand your query. Can you elaborate it please ?

    Reddy: Sorry I should have explained what I want to do before.

    I want to calculate number of business days so I don't want to include Saturdays and Sundays.
    Also for calculating number of business days I want to use Monday as start date(if the start date entered is sat or sun)
    and use Friday as the end date(if the end date entered is sat or sun), for any other day use that date as start and end date.

    I have also written function to do this:


    Create or replace function num_Business_Days(start_date in date, end_date in date)
    return number is
    currdate date := start_date; /* holds the next date */
    theDay varchar2(10);/* day of the week for currdate */
    countBusiness number := 0; /* counter for business days */

    begin

    loop

    /* finished if end_date is reached */
    exit when currdate = end_date+1;

    /* what day of the week is it? */
    select to_char(currdate,'fmDay') into theDay from dual;

    /* count it only if it is a weekday */
    if theDay <> 'Saturday' and theDay <> 'Sunday' then
    countBusiness := countBusiness + 1;
    end if;

    /* go to the next day */
    currdate := to_date(currdate+1);

    end loop;
    return (countBusiness);
    end;
    /

    But I am not sure if this is efficient way of doing it because it loops through each day between start and end date.

    thanks
    Sonali
    Sonali

  6. #6
    Join Date
    Oct 2000
    Posts
    123
    In PL/SQL you can use the logic:

    select count( * ) days_business
    from (
    -------select rownum-1 r
    -------from all_objects
    -------where r <= date1 - date2 )
    where to_char( date2 + r - 1, 'DY' ) not in ( 'SAT', 'SUN' );

    Note: date1 is second date while date2 is first date.

    Make modification for i'm not tested it(of my hat), but it works for me before.

    Take care

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