Re: Caculating "business days" in Oracle

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Thu, 19 May 2005 21:42:48 +0200
Message-ID: <428ceb58$0$21642$ba620e4c@news.skynet.be>

bbcrock_at_gmail.com wrote:
> My solution to calculating the number of business days using Oracle 8i
> is to create a table with holidays and run some kind of calculation
> like so:
>
> 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 table_of_holidays where no_work_day =
> trunc(to_date('&2')+rnum-1) )
> /
>
> If anyone knows of an easier solution that doesn't require a separate
> holiday table, let me know.
>
> thanks,
>
> Don
>

Not easier, but a hell of a lot faster (without using that foolish all_object), but you'll still need the holiday table though.

Search Metalink for "workdays", I've posted it there. Received on Thu May 19 2005 - 14:42:48 CDT