calculating the number of working days between 2 dates

[csunix]

Does anyone have a function created that would allow me to calculate the number of working days( fiscal days) between 2 dates

 

[hoinz]

Did you try search button?
This was asked before.

 

[SantaMufasa]

CS,

I agree with Hoinz: Search is a good thing to try, but unfortunately, Tek-Tips's Search doesn't always turn up what you want/need, so, as a convenience, I'll post my resolution for you, hoping that it does what you want:

Section 1 -- Invocation of WEEKDAY_DIFF function:

******************************************************************************
col a heading "Difference|In WeekDays" format 99,999
select weekday_diff(to_date('Fri, 05 May 2006','DY, dd Mon YYYY'),
                    to_date('Mon, 15 May 2006','DY, dd Mon YYYY'))a from dual;

 Difference
In WeekDays
-----------
          7

Section 2 -- Definition of WEEKDAY_DIFF function:

create or replace function weekday_diff(dt1 date,dt2 date) return number is
    curr_dt  date;
    day_cnt  number := 0;
    date_beg date;
    date_end date;
begin
    date_beg := least(dt1,dt2);
    date_end := greatest(dt1,dt2);
    curr_dt  := date_beg;
    while curr_dt <= date_end loop
        if to_char(curr_dt,'DY') not in ('SAT','SUN') then
            day_cnt := day_cnt + 1;
        end if;
        curr_dt := curr_dt + 1;
    end loop;
    return day_cnt;
end;
/

Function created.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]