Re: Date Routines

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1997/12/22
Message-ID: <882821083.1020592136_at_dejanews.com>#1/1

In article <349E9183.736D_at_inficad.com>,   D&L <djk_at_inficad.com> wrote:
>
> I'm seeking an Oracle routine that counts business days between two
> dates, excluding Saturdays, Sundays, and holidays as listed in some
> pre-determined table. Or, similarly, if there is a routine that
> takes a start date, a number of business days, and then computes the
> resulting date based on excluding weekends and holidays (that latter
> routine is actually the one we would use the most).
>
> Any ideas?
>
> --Dave

Here is a SQL Plus routine I put together and posted in March of this year. It calculates the business days, but uses a number-of-seconds-since... as the beginning and ending dates. You could modify it to use a database date column.

You could also modify it to address holidays just by doing the calculation given, and then subtracting the number of holidays returned from a:

   Select count(*) from holiday_table

      where holiday between start_date and end_date;

Regards,
Steve Cosner
http://members.aol.com/stevec5088

ps. There is another post under the same thread giving another (more complicated) solution. You can look it up using Deja News.

--------copy of old post starts here---------------------------

Subject:      Re: Calculating "working days" for a given period
From:         stevec_at_zimmer.csufresno.edu (Steve Cosner)
Date:         1997/03/31

Message-ID: <859836376.11471_at_dejanews.com> Newsgroups: comp.databases.oracle.misc

In article <33311E44.6C87_at_citicorp.com>,   andy.smith_at_citicorp.com wrote:
> Appreciate if anyone has a script that will calculate the number
> of working days, given a starting and ending date.
>
> Both starting and ending date is stored as a number of seconds since
> 1st June 1970.
>
> e.g
>
> TABLE
> -----
> Startdate NUMBER(15)
> Enddate NUMBER(15)

Here's something that will work:

--
-- The following is a SQL Plus script that calculates the number of
-- working days between two dates.  It is the total number of
-- days minus all Saturdays and Sundays.  If the start and end dates
-- are equal, 1 workday is returned (unless date is Saturday or Sunday).
-- Value returned is incorrect if EndDate is less than StartDate.
-- This does not address holidays.
--
-- Steve Cosner stevec_at_zimmer.csufresno.edu
-- http://members.aol.com/stevec5088
--
------------------------------------------------------------------------

Variable T Varchar2(100);
Declare
  StartDate Number := 846547200; --Seconds since 01-Jun-1970 = 29-Mar-97
  EndDate   Number := 847238400; --                          = 04-Apr-97
  Refd  Date  :=  To_Date('01-Jun-1970','DD-MON-YYYY');
  StDt  Date;     -- Start date
  EnDt  Date;     -- End date
  Tot   Integer;  -- Total days, including weekends
  P     Integer;  -- Partial week
  D     Integer;  -- Day number:  M=1, T=2... Sat=6, Sun=7
  F     Integer;  -- Weekend factor (# of days in P which are Sat or Sun
  Btwn  Integer;  -- Weekdays between the two dates--includes both
                  -- first and last day.  If first=last, btwn=1.
Begin
  -- Convert number of seconds to dates --
  StDt:= Refd + Trunc(StartDate/86400);
  EnDt:= Refd + Trunc(EndDate/86400);
  -- Get total number of days --
  Tot := EnDt - StDt + 1;
  -- Adjust for Partial week, and subtract Sat. and Sun.
  P   := Mod(Tot,7);
  D   := To_Number(To_Char(StDt,'D'));
  D   := D-1+7*(1-Least(1,D-1));
  F   := Greatest(Least(0,Greatest(-2,6-D-P)),-Mod(D,7)-1);
  -- Do the final calculation:  5 times # of weeks,
  -- plus the odd days (partial week), except for Sat. and Sun. if they
  -- are included in the partial week.
  Btwn:= 5*Trunc(Tot/7)+Greatest(0,P+F);
  -- Display the result
  :T := 'There are '||To_Char(Btwn)||' working days between '
        ||To_Char(StDt,'Dy, DD-Mon-YYYY')
        ||', and '||To_Char(EnDt,'Dy, DD-Mon-YYYY'||'.');
End;
 .
/
print t

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Mon Dec 22 1997 - 00:00:00 CET