date format conversion

From: Arsene Wald <Arsene.Wald_at_act.etat.lu>
Date: Mon, 29 Nov 1999 17:38:48 +0100
Message-ID: <3842AC18.5562F5A2@act.etat.lu>

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 Received on Mon Nov 29 1999 - 10:38:48 CST