Re: Working days between two dayes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 10 Aug 1999 13:23:30 GMT
Message-ID: <37bf2778.91471028@newshost.us.oracle.com>

A copy of this was sent to "Ben Mackie" <mackieb_at_logica.com> (if that email address didn't require changing) On Thu, 5 Aug 1999 11:12:07 +0100, you wrote:

>Hi,
>
>Is there an Oracle function to calculate working days
>between two dates?
>
>Or any function that may simplify this task?
>
>Let me know if there is, thanks
>
>Ben.
>

Working days is in the eye of the beholder... Here is the general method for doing such a thing though:

SQL> /*
DOC> * consider this query.  it generates a row for each day in
DOC> * range (replace 01-jan-1999 with your start, 15-jan-1999
DOC> * with your end).  You just need a table with N rows such
DOC> * that N > max difference between 2 dates you expect.
DOC> */
SQL> 
SQL> 
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.

SQL> 
SQL> /*
DOC> * Once we have that, counting monday-fridays is easy:
DOC> */
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 /

COUNT(*)

SQL> 
SQL> 
SQL> /*
DOC> * (to_char(day,'d') returns a number from 1-7 representing
DOC> * the day of the week. Now, if some day or days the office
DOC> * is closed AND you have a table
DOC> * of these days (holidays and such), we can add that in as well
DOC> */
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(*)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 10 1999 - 08:23:30 CDT