Re: Uppercase constraint on Date Field

In article <19970319055301.AAA10672_at_ladder01.news.aol.com>, LizRHW <lizrhw_at_aol.com> wrote:
>I have inherited a database with many date fields having a constraint
>forcing upper case. This would seem to have no effect since there is not
>really a case associated with a date. However...
>
>create table testtable (col1 date);
>alter table testtable add constraint update check (col1=upper(col1));
>insert into testtable values ('01-jan-97');
> <this works fine, even though it is lower>
>insert into testtable values (to_date('01-jan-1998','dd-mon-yyyy'));
> <works fine>
>insert into testtable values (to_date('01-jan-2000','dd-mon-yyyy'));
> fails, with the uppercase constraint violated.
>Any ideas (other than dropping the contraints...)?
>Dan

Dan, just drop the constraint! It is COMPLETELY useless. Dates are stored internally without any upper/lower case characters involved. But you know that.

Your constraint is probably tripping over the way Oracle is trying to convert the date to a character value before making the comparison. It's probably converting the date to character and back to date again, or something like that, using DD-MON-YY one place and DD-MON-RR another, somehow getting the wrong century.

If you tell the constraint exactly how to convert the dates to character format, then the constraint won't complain on 01-jan-2000:

 alter table testtable add constraint updt check    (to_char(col1,'DD-MON-YYYY')=upper(to_char(col1,'dd-mon-yyyy')));

But I still can't imagine why on earth you would want such a useless thing! The above constraint will ALWAYS be true, no matter what the date.

Regards,
Steve Cosner
http://members.aol.com/stevec5088 Received on Thu Mar 20 1997 - 00:00:00 CET