Re: External Tables and date formats

Yes, I think that Oracle was interpretting "200-12-01" as the year 200 A.D. I wanted Oracle to throw the date out as "invalid format". I eventually managed to solve this using "FXYYYY-MM-DD" format ie: Format eXact.

It wasn't obvious to me from the documentation that the driver for external tables was SQL "date format aware" - I'd also forgotten about the "FX" mask.

Problem solved.

M.

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<1wNTb.83319$U%5.447317_at_attbi_s03>...
> "Matt" <mathewbutler_at_yahoo.com> wrote in message
> news:19f48a45.0402030030.63e205ec_at_posting.google.com...
> > I am having problems getting the external table functionality in 9i to
> > work correctly with dates (maybe it just doesn't work as I expect?).
> >
> > Using the following syntax and a reject limit of zero:
> >
> > col1 char(10) date_format date mask "RRYY-MM-DD",
> > col2 date(10) mask "RRYY-MM-DD",
> > col3 char(10) date_format date mask "YYYY-MM-DD",
> > col4 date(10) mask "YYYY-MM-DD",
> >
> > I can't seem to get records such as:
> >
> > 200-12-01
> >
> > to cause a failure. Instead the record gets loaded as 2000-12-01
> >
> > Can anyone kindly point me in the direction of the syntax I am looking
> > for (metalink and the doco have been no help so far).
> >
> > Thanks in advance.
>
> Does 200 mean the year 200 AD? I am familiar with 2000 or 00 for they year
> 2000 but not 200 for 2000.
> Jim
Received on Sat Feb 07 2004 - 00:34:32 CST