Calculate Working Days in SQL

[TimboA]

I need to calculate the number of working days between two dates using pure SQL (ie can't use WORK_DAYS). Anyone know how this can be achieved??

many thanks in advance.

timboa

 

[Darsheva]

Try this:

-- --------------------- Begin ----------------------------

Set feedback off echo off autop off ver off

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

accept MONTH char format 'A3'-
prompt'Enter MONTH for which U want the number of working days (like JAN..):'

accept YEAR char format 'A4'-
prompt'Enter the 4 digit Year (like 1999...):'

select count(*) NUM_WORK_DAYS
from (select to_date( '01-&MONTH-&YEAR', 'dd-mon-yyyy' )+rownum-1 day
from all_objects
where rownum < to_number( to_char( last_day( '01-&MONTH-&YEAR' ),'DD' ) )+1)
where to_number(to_char(day,'d')) between 2 and 6
/

-- ------------------------ End ---------------------------