Re: Date Arithmetic - Number of Work Days

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1996/12/11
Message-ID: <58mrn9$sfs@shadow.CSUFresno.EDU>#1/1

In article <58livn$4750_at_elmo.cadvision.com>, Ted Goulden <gouldent_at_cadvision.com> wrote:
> 1. Does anyone know how to calculate the number of work days between
>two specified dates. Work days would, of course, exclude weekends and
>statutory holidays. It seems like a simple problem until Good Friday
>or situations like Christmas occuring on a Sat/Sun are considered.
>
> 2. This functionality is required to determine if contractual lead
>times are being met, so an accurate method is required.
>
> 3. I am looking for something that could be coded in PL/SQL or an SQL
>statement. If possible, I would like it to be maintenance free for the
>next 5-10 years.

I would create a holiday table which contains a row with the date for each non-weekend holiday that you need to subtract in the calculation. Then you could select from the table a count of days that fall between the your start and end dates. Just subtract this count from your work days (work_days=total_days - weekends), and you have the correct number.

Just keep the table updated with each year's specific dates, and be sure to exclude weekend holidays.

HTH,
Steve Cosner Received on Wed Dec 11 1996 - 00:00:00 CST