Re: Uppercase constraint on Date Field

On 19 Mar 1997 05:53:37 GMT, in comp.databases.oracle.tools you wrote:

>...
>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-2000','dd-mon-yyyy'));
>
>fails, with the uppercase constraint violated.
>
>Any ideas (other than dropping the contraints...)?

Curious. I'll have to give it a try tomorrow. Have you done any testing to see just what effect, if any, uppercasing has on a date? I would at least try:

   select upper(to_date('01-jan-2000','dd-mon-yyyy')),

              to_date('01-jan-2000','dd-mon-yyyy')
      from dual;

just to see what happens. Curious that the problem occurs with year 2000 dates. I wonder if that has anything to do with the rational for creating this constraint in the first place.

Hmmm... I wonder if Oracle does an implicit conversion to character when evaluating upper(date_col). Are you really evaluating upper (to_char(date_col))? Perhaps you should:

	select to_char(to_date('01-jan-2000','dd-mon-yyyy')) 
          from dual;

and see what comes back. Gosh I wish I had Oracle at home<g>!

regards,

Jonathan

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Thu Mar 20 1997 - 00:00:00 CET