Thank you very very much for your help!
Best regards,
Peter
Thomas Gaines <Thomas.Gaines_at_noaa.gov> wrote in message news:<410039AB.8000504_at_noaa.gov>...
> Peter -
>
> After reading David's response, I realized that I
> mistakenly considered the hour:minute mask as the
> input instead of the actual time. With that in
> mind, I'll amend my example as follows.
>
>
> drop table xxxx;
> create table xxxx (mydate varchar2(10),
> data varchar2(30));
>
> insert into xxxx values ('13:00','row 1');
> insert into xxxx values ('7:03','row 2');
> insert into xxxx values ('27:00','row 3');
> insert into xxxx values ('13:00:05','row 4');
>
>
> -- Create the test function
>
> create or replace function check_date_format (time_field varchar2)
> return number is
> dummy_date date;
> begin
> -- This function will do a dummy date assignment using the
> -- passed-in date format. If an error occurs, then the date
> -- format is no good and the "bad" value is returned.
> -- Else, the "good" value is returned.
> dummy_date := to_date('07/21/2004 ' || time_field,'mm/dd/yyyy hh24:mi');
> return 1;
>
> exception
> when others then
> return 0;
>
> end;
> /
>
> -- And then do some test queries using it.
>
> select 'wrong date format', 'xxxx', data
> from xxxx
> where check_date_format(mydate) <> 1;
>
>
> I thought that I would be able to use this
> user-defined function in a check constraint on this
> particular column. But when I try it, I error with
> an ORA-00904: "CHECK_DATE_FORMAT": invalid identifier.
> I haven't checked the docs, but I think that one is not
> allowed to do this. No big deal, though. Just create
> a trigger and do the check there...
>
>
>
> Peter Mueller wrote:
>
> >Hi,
> >
> >I would like too know, if somebody has an idea on how to select
> >all datasets that do not match the following criteria:
> >
> >column_name datatype format example
> >mydate varchar(10) HH24:MI 13:00
> >
> >What I would like to do is:
> >
> >insert into problem_records (reason, tablename, data)
> >select 'wrong date format', 'xxxx', 'yyyy'
> >from xxxx
> >where mydate <> ??????does not match format?????
> >
> >I thought I could use a check in the database schema like:
> >check( to_date( mydate, 'HH24:MI' ) ), but oracle kept complaining
> >about using that function in a check statement. That's why I would
> >like to write something that checks this format. It would be nice,
> >if I do not have to use PL/SQL.
> >
> >I hope somebody can help me.
> >
> >Best regards,
> >lordi
> >
> >
Received on Mon Jul 26 2004 - 03:40:47 CDT
