Date validation in trigger 2

[saustin]

Hi,
Users can enter dates in a text field in either 2yr or 4 year format. Would like to check the value of these in a trigger for a valid date range but what is the best way to do so ? For example,

DECLARE l_test DATE;

BEGIN

IF :NEWDATA.USER_1 > ' ' Then
l_test := TO_DATENEWDATA.USER_1, 'MM/DD/YYYY');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-220000, 'Contract Date is not a Valid Date');
END;

This works great for a four year date format but skips the 2year format. Just a length check ?

Thanks, Steve.

 

[SantaMufasa]

Yes, Steve, but, why not use a DATE data type to store your dates? Not using a DATE column to store dates is very bad form in Oracle and usually is completely unjustifiable.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[saustin]

Hi Dave,
Why naturally because it is a 3rd party legacy app we support and have no choice over for the next year.

 

[Thargy]

Steve,

my sympathies. This is not a subject about which I have any strong feelings, but if I was "king for a day", I'd make it legal to shoot everyone who allows direct date entry in an application, instead of insisting on selection from a date control. Anyone who didn't store dates as dates, would be let off lightly with a flogging, followed by disembowellment, and then slowly being roasted alive.

Maybe we should start a thread as to how best to exterminate such superfluous members of the population?

<Descends from soap box>

I have to agree with skinny Santa (ask him about that). Keep the trigger simple, plug in the two missing characters if needed, and insert into a date type column. That way, Oracle is doing the validation, and not you and your trigger.

I wonder how many times this sort of thing will be posted before the message gets across to system designers? Grrrr.

T.


Grinding away at things Oracular

 

[carp]

While I agree with all of the above sentiments about bad design, I can certainly sympathize with Steve's situation - we just absorbed another company AND all of their horrific code, and are going through the delousing process now.

So, back to the original question - how about something like this:

DECLARE l_test DATE;
BEGIN
  IF :NEWDATA.USER_1 > ' ' Then 
    BEGIN
      l_test := TO_DATE(:NEWDATA.USER_1, 'MM/DD/YYYY');
    EXCEPTION
      WHEN OTHERS THEN 
        BEGIN
          l_test := TO_DATE(:NEWDATA.USER_1, 'MM/DD/YY');
        END;
    END;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-220000, 'Contract Date is not a Valid Date');
END;

 

[saustin]

Hi All,
Carp many thanks for the moral support. Will just add
a date range check to the code. Right now it will reject text but you could put in 1/1/907 with no error.

Steve.

 

[Beilstwh]

A better way would be

DECLARE l_test DATE;
BEGIN
IF :NEWDATA.USER_1 > ' ' Then
BEGIN
l_test := TO_DATENEWDATA.USER_1, 'MM/DD/RRRR');
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-220000, 'Contract Date is not a Valid Date');
END;
end;


The RRRR should correctly convert a two digit or 4 digit year.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Steve,

By no means am I hammering on you or impugning your methods...I have known for years now, that you are a professional and would never allow this substandard behaviour to occur if it was within your power to combat it. And, I am aware that there are software companies out there that "flog their substandard crap" on unsuspecting customers.

My plea is that we, as professionals, absolutely demand of any software providers that their code meet some minimum standards, below which your application falls. That is part of our due diligence as customers.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[saustin]

No problemo Dave,
Don't even get me started on the "long raw" field types the vendor fell in love with.

Best regards, Steve.

 

[carp]

Steve -
I would go with Bill's excellent approach! While it will still let 907 in as a valid year, it's a lot simpler!
Have a star, Bill!

 

[saustin]

SOLVED, fyi this handles 2yr and 4yr date checking (this century only)in a text field.

DECLARE bad_year EXCEPTION;

BEGIN

IF :NEWDATA.USER_1 > ' ' Then

if TO_CHAR(TO_DATENEWDATA.USER_1, 'MM-DD-RRRR') ,'YYYY') > '2100' OR TO_CHAR(TO_DATENEWDATA.USER_1, 'MM-DD-RRRR') ,'YYYY') < '2000' then
raise bad_year;
end if;
END IF;
EXCEPTION
WHEN bad_year Then
raise_application_error(-220000, 'Contract Date is not a Valid Date');
END;


Many thanks to all tek-tippers.