Business days

Here is a general method for finding the business days between two known date ranges

First look at the output of this query.

SQL> select day, to_char(day,'d'), to_char(day,'day')
  2    from (
  3  select to_date( '01-JAN-1999', 'dd-mon-yyyy' )+rownum-1 day
  4    from all_objects
  5   where rownum < to_date( '15-jan-1999','dd-mon-yyyy') -
  6                  to_date( '01-jan-1999','dd-mon-yyyy') + 2
  7   )
  8  /

DAY       T TO_CHAR(D
--------- - ---------
01-JAN-99 6 friday
02-JAN-99 7 saturday
03-JAN-99 1 sunday
04-JAN-99 2 monday
05-JAN-99 3 tuesday
06-JAN-99 4 wednesday
07-JAN-99 5 thursday
08-JAN-99 6 friday
09-JAN-99 7 saturday
10-JAN-99 1 sunday
11-JAN-99 2 monday
12-JAN-99 3 tuesday
13-JAN-99 4 wednesday
14-JAN-99 5 thursday
15-JAN-99 6 friday

15 rows selected.

Any table with sufficient number of rows will do. If you're obsessed with performance you could create a tightly packed cached table with a single numeric column. From the above example, counting monday-fridays is easy:

SQL> select count(*)
  2    from (
  3  select to_date( '01-JAN-1999', 'dd-mon-yyyy' )+rownum-1 day
  4    from all_objects
  5   where rownum < to_date( '15-jan-1999','dd-mon-yyyy') -
  6                  to_date( '01-jan-1999','dd-mon-yyyy') + 2
  7   )
  8   where to_number(to_char(day,'d')) between 2 and 6
  9  /

  COUNT(*)
----------
        11

If you're concerned about holidays etc, as these are stored in an available table, then these can be factored in as well.

SQL> 
SQL> create table days_off ( the_date date primary key );

Table created.

SQL> 
SQL> REM Happy New Years
SQL> insert into days_off values ( to_date('01-JAN-1999','dd-mon-yyyy') );

1 row created.

SQL> 
SQL> 
SQL> 
SQL> select count(*)
  2    from (
  3  select to_date( '01-JAN-1999', 'dd-mon-yyyy' )+rownum-1 day
  4    from all_objects
  5   where rownum < to_date( '15-jan-1999','dd-mon-yyyy') -
  6                  to_date( '01-jan-1999','dd-mon-yyyy') + 2
  7   )
  8   where to_number(to_char(day,'d')) between 2 and 6
  9     and not exists ( select NULL
 10                        from days_off
 11                       where days_off.the_date = day )
 12  /

  COUNT(*)
----------
        10

See the original newsgroup post here