There are a couple of things to consider, here. If you calculate the working days, using only Saturday and Sunday, you will be counting holidays. Many companies will create a "holiday calendar" type table as well.
This solution was actually published in Oracle's ASK TOM resource recently.
The code: select count(*) from ( select rownum as rnum from all_objects where rownum <= to_date('&1') - to_date('&2')+1 ) where to_char( to_date('&2')+rnum-1, 'DY' )not in ( 'SAT', 'SUN' );
It calculates work days, exclusive of holidays.
In the example, you use a table that has as many rows as the number of days you may span. For instance, if there is one year between the dates, the table you reference must have 366 rows. Because all_objects is a large table, it's a good 'generic' candidate (as Tom says).
If you create and use a table with holiday dates in it...
create table exclude_dates ( no_work date primary key ) organization index; insert into exclude_dates values ( '14-FEB-2000' );
...then you can then add the "not exists.." clause to check whether the date is a work day.
select count(*) from ( select rownum rnum from all_objects where rownum <= to_date('&1') - to_date('&2')+1 ) where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' ) and not exists ( select null from exclude_dates where no_work = trunc(to_date('&2')+rnum-1) );
Dig Deeper on Oracle E-Business Suite
Related Q&A from Carol Francum
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.