A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Number of business days between two dates
A recent question of a technical forum interested me, the questioner in an Oracle 10 environment wanted to know the number of business days (i.e excluding Saturday and Sunday) between two dates (5-Feb-2007 and 01-Jan-2007 , the answer is 26 incidentally) and he wanted to do it just using bare SQL - no procedural code. Not quite as easy as it first seems
select count(*) from(
select to_date('05-feb-07') -level + 1 dte
connect by level <>= '01-jan-07'
and to_char(dte,'dy') not in ('sat','sun')
The number in the connect by level comparison must be greater than the total number of days between the two dates you are testing against. In practice this isn't a problem as even setting this to 100000 for instance i.e going on for 300 years the response time is still reasonable.
Next though the OP wanted to know how to use the above code to do the same for dates held in a table. Again this is not quite as easy as it first appears.
SQL> select dte2 end_date, dte1 start_date from tom
1 select distinct count(*) over(partition by dte2,dte1) + 1 working_days,
2 dte2 end_date,dte1 start_date
3 from (select level x from dual
4 connect by level <>
5 where x <= dte2 - dte1
6* and to_char(dte1+x,'DY') not in ('SAT','SUN')
WORKING_DAYS END_DATE START_DAT
------------ --------- ---------
1094 17-AUG-05 08-JUN-01
26 05-FEB-07 01-JAN-07
262 13-MAR-07 13-MAR-06
711 01-DEC-08 13-MAR-06
The number in the connect by level <> than the max number of days between any start/end date
Duplicate start/end dates in the tables will cause it to break.