Re: removing weekends?

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/03/06
Message-ID: <331F0046.1AD1@lilly.com>#1/1

John Hoskins wrote:
>
> Hi,
> I want to subtract two dates, and get the number of 'working' days, in
> other words not count the weekend days. Is their a way to do this in the
> select clause? Or will I need to do it in more than one step.
> Any help would be appreciated,
> John Hoskins
> john.hoskins_at_teldta.com

You could do it in a the select clause but it would be rather hairy. I would suggest writing stored function to do it in PL/SQL, then use the function in your SQL statement. For example:

CREATE OR REPLACE FUNCTION working_days_between

   (date1 in DATE, date2 IN DATE)
RETURN NUMBER IS

• calculate and return value END IF;

Then if you wanted to issue a SQL statement that found all orders in your system that took more than 5 working days to ship you could issue the following SQL statement. Keep in mind that if you put this function in a package you will also need to put a PRAGMA RESTRICT_REFERENCES in the package specification (I don't think you do this for a standalone function).

SELECT order_id, order_desc
  FROM orders
 WHERE working_days_between(order_date, ship_date) > 5;

Chris Halioris
Tactics, Inc.
hali_at_tacticsus.com Received on Thu Mar 06 1997 - 00:00:00 CST