Re: Dates validation

From: Mikhail Rezhepp <mrezhepp_at_best.com>
Date: 1997/03/27
Message-ID: <333A0626.3FF5_at_best.com>#1/1


Nico Vandenbroucke wrote:
>
> We have an application in which many tables have date range columns (start
> and end date).
>
> We have to check a lot if a certain date falls within the date range, or if
> two date ranges are not overlapping or if a date range falls within another.
>
> This means a lot of ' date_x between start_dt and end_dt 'stuff in the SQL
> statements.
>
> Does any1 know of a (brilliant) way to avoid or minimise all this checking?
>
> regards
> Nico

Nico,
Maybe this way is not brilliant, but at least it works. 1. Create a stored procedure (with parameters) which will include all the necessary logic for your date checks. If this procedure locates abnormal condition it invokes an error

   IF bla bla bla THEN
     raise_aplication_error(-20100,'Dates not within specified range');    END IF;
You can then handle this error condition in any application 2. Define BEFORE INSERT OR UPDATE triggers on tables in the database from which this procedure is called.
3. Now you may be sure that no one row violating your rule would get into your database.
Best regards,
Mikhail Rezhepp Received on Thu Mar 27 1997 - 00:00:00 CET

Original text of this message