Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: date format conversion
In article <3842AC18.5562F5A2_at_act.etat.lu>,
Arsene Wald <Arsene.Wald_at_act.etat.lu> wrote:
> The following specification from an SQL*Loader control file:
> MOMENT POSITION(26:31) DATE "YYMMDD",
>
> with the following lines of data:
> ... 98 6 9 ...
> ... 98 610 ...
>
> produces the following error for the second data line:
> Record 2: Rejected - Error on table BLABLA, column MOMENT.
> ORA-01843: not a valid month
>
> whereas the first line is correctly interpreted as 09-JUN-98!
> So if both Month AND Day have one blank followed by one digit it's
OK, but if
> Day is two digits and Month is one blank one digit there's a problem!
>
> SQL*Plus has the same behaviour:
>
> SQL> SELECT to_date('98 6 9','YYMMDD') from dual;
>
> TO_DATE('
> ---------
> 09-JUN-98
>
> SQL> SELECT to_date('98 610','YYMMDD') from dual;
> ERROR:
> ORA-01843: not a valid month
>
> no rows selected
>
> SQL>
>
> Any workaround known?
> TIA,
> Arsene WALD
>
I do not write sqlldr statements very often but have you tried defining
the field as 'YY MM DD'. I believe that if you tell sqlldr that the
sections of the data are blank separated it will handle the format
correctly.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Nov 29 1999 - 14:14:45 CST