How To Exclude Weekends? 1

[marksmithy69]

Hello everyone. Is there an easy way to get the get the difference between 2 dates, but exclude weekends from the difference? Thanks again.

 

[carp]

I'm not aware of a way to do this in SQL (although I'm sure it can be done), but here is a rather straightforward function you can install on your database:

CREATE OR REPLACE FUNCTION no_weekends(p_date1 IN DATE, p_date2 IN DATE) RETURN NUMBER IS
   l_count NUMBER := 0;
   l_date1 DATE := LEAST(p_date1, p_date2);
   l_date2 DATE := GREATEST(p_date1, p_date2);
BEGIN
   WHILE (l_date1 <= l_date2) LOOP
      IF (TO_CHAR(l_date1,'DY') NOT IN ('SAT','SUN')) THEN 
         l_count := l_count + 1;
      END IF;
      l_date1 := l_date1 + 1;
   END LOOP;
   RETURN l_count;
END no_weekends;
/

Then, you can just use the function in your SQL:

SQL> select no_weekends('22-SEP-07','15-OCT-07') from dual;

NO_WEEKENDS('22-SEP-07','15-OCT-07')
------------------------------------
                                  16

 

[Dagon]

From SQL, you could use something like:

select count(*) from
(select to_char(to_date('22-sep-07', 'DD-MON-YY')+level, 'DY') as dy
from dual
connect by level <= (to_date('15-oct-07', 'DD-MON-YY') - to_date('22-sep-07', 'DD-MON-YY')))
where dy not in ('SAT', 'SUN')

 

[SantaMufasa]

In addition to Carp's excellent code and Dagon's very clever approach, I'll post my "Weekdays" function, which runs equally quickly with Carp's and Dagon's code (i.e., "Elapsed: 00:00:00.00"), but my objective was to produce the tightest code/fewest number of statements while providing the code in a user-defined function. (And, as with Carp's code, it doesn't matter which date you put in which position.):

create or replace function weekdays (dt1 date, dt2 date) return number is
    weekday_count number;
begin
    select count(*) into weekday_count
      from (select rownum rn from all_tab_columns
             where rownum <= greatest(dt1,dt2)-least(dt1,dt2))
     where least(dt1,dt2)+rn <= greatest(dt1,dt2)
       and to_char(least(dt1,dt2)+rn,'DY') not in ('SAT','SUN');
    return weekday_count;
end;
/

Function created.

select weekdays('22-SEP-07','15-OCT-07') weekdays from dual;

WEEKDAYS
--------
      16

Cheers!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marksmithy69]

I just wanted to thank you guys for the excellant code. It was a great help. How can I modify it to include decimal places as well. For example 3.69 days. Thanks again.

 

[SantaMufasa]

We can demonstrate such code modifications if you can explain what the ".69" represents, along with sample input data with counterpart output.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marksmithy69]

Well, for example if I wanted to get the difference between 6/8/2007 2:56:31 PM and 6/12/2007 12:23:23 PM. Thanks again.

 

[SantaMufasa]

Here, then, Mark, is the code with proper adjustments to meet your needs:

create or replace function weekdays (dt1 date, dt2 date) return number is
    weekday_count number;
begin
    select (greatest(dt1,dt2)-least(dt1,dt2))-count(*) into weekday_count
      from (select rownum rn from all_tab_columns
             where rownum <= (greatest(dt1,dt2)-least(dt1,dt2))+1)
     where trunc(least(dt1,dt2))+rn <= trunc(greatest(dt1,dt2))
       and to_char(least(dt1,dt2)+rn,'DY') in ('SAT','SUN');
    return weekday_count;
end;
/

Function created.

select weekdays(
                to_date('6/8/2007 2:56:31 PM','mm/dd/yyyy hh:mi:ss pm'),
                to_date('6/12/2007 12:23:23 PM','mm/dd/yyyy hh:mi:ss pm')
               ) diff
from dual;

      DIFF
----------
1.89365741
*************************************************************************

Let us know if this takes care of business.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marksmithy69]

Thanks for your reply. That takes care of the decimals, but I am not sure that the calculation is correct. For example, if I put in 6/8/2005 2:56:31 PM and 6/12/2005 12:23:23 PM, it says 1.89365741. That doesn't seem to be correct, since there are at least 2 work days between those 2 dates. Thanks again.

 

[SantaMufasa]

You are correct, Mark -- The code needed correction/adjustment. Making the changes requires extra code that makes the final result not as tight as the original code set, but it delivers reliable, correct results now:

create or replace function weekdays (dt1 date, dt2 date) return number is
    weekday_count number;
    beg_dt date := least(dt1,dt2);
    end_dt date := greatest(dt1,dt2);
    frags number;
begin
    if trunc(end_dt) = trunc(beg_dt) then
        if to_char(end_dt,'DY') in ('SAT','SUN') then return 0;
        else return end_dt-beg_dt;
        end if;
    else
        if to_char(beg_dt,'DY') in ('SAT','SUN') then frags := 0;
        else frags := (trunc(beg_dt)+1) - beg_dt;
        end if;
        if to_char(end_dt,'DY') in ('SAT','SUN') then null;
        else frags := frags + (end_dt - trunc(end_dt));
        end if;
        select frags+count(*) into weekday_count
          from (select rownum rn from all_tab_columns
                 where rownum <= ((end_dt-beg_dt)+10))
         where trunc(beg_dt)+rn <= trunc(trunc(end_dt)-(1/24/60/60))
           and to_char(trunc(beg_dt)+rn,'DY') not in ('SAT','SUN');
        return weekday_count;
    end if;
end;
/

Function created.

Let us know if you see any remaining issues.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marksmithy69]

Works great. Thanks a lot everyone for your help.