Re: Help Loading YYYYMMDD Dates

From: Alvin Law <alvinlaw_at_netcom.com>
Date: 1995/05/19
Message-ID: <ALVINLAW.95May18193114_at_netcom23.netcom.com>#1/1

In article <3pdgjc$msu_at_newsbf02.news.aol.com> roywagner_at_aol.com (Roy Wagner) writes:

> I'm am trying to load dates from a file that has them in the format
> YYYYmMdD, where a lower case letter represents a blank if the month or
> year is < 10. I do not what to HAVE TO reformat these dates in the flat
> files that I need to transfer to ORACLE 7.1. However, this examples shows
> what happens when I try and load dates with blanks. ORACLE seems to skip
> the blank on the month's first column and attempt to read if from the
> first column of the day columns. What is causing this and how can I load
> dates in this format without changing their format in the flat file.
>
> Thanks to anyone that can help.
>
> - Roy Wagner (a new ORACLE user) -
>
> PS: Using Personal ORACLE 7.1
>
> ------------
> rem TESTDATE.SQL
>
> DROP TABLE testdate
>
> CREATE TABLE testdate
> (testdate DATE)
>
> ------------
> rem TESTDATE.CTL
>
> LOAD DATA
> INFILE 'c:\testdate.dat'
> REPLACE
> INTO TABLE testdate
> (testdate POSITION (1:8) DATE(8) "YYYYMMDD")
>
> ------------
> rem TESTDATE.DAT
>
> 1991 1 1
> 1992 6 2
> 199312 3
> 1991 111 <-- Loads this date wrong.
> 1992 622 <-- Rejects this date.
> 19931230
>
> ------------
> rem TESTDATE.LOG
>
> Record 5: Rejected - Error on table TESTDATE, column TESTDATE.
> ORA-01843: not a valid month
>
> ------------
> SELECT * FROM TESTDATE;
>
> TESTDATE
> ---------
>
>
> 01-JAN-91
> 02-JUN-92
>
> 03-DEC-93
> 01-NOV-91 <-- Should be 11-JAN-91
> 30-DEC-93
>
>
> ---------

I try this with SQL*Plus (Oracle 7.1.6.2 on a Sun) and get the following result:

SQL> select to_date('1991 111','YYYYmMdD') from dual;

TO_DATE('

---

01-NOV-91 So the behavior is normal. It could be a bug in the Oracle kernel but most likely it's the way ANSI SQL works.

To solve your problem, you can load the value into a VARCHAR2 column and write a database trigger to populate the date field with a format less ambiguous, like YYYMMDD. Of course on a VLDB this could be a major performance bottleneck but on PO7, it shouldn't be that bad.

-- 
"And this is all I have to say about that..." - F. Gump

                                    `o<'
---------------------------------oo0(__)0oo--------------------------------
   __    __  __  __  __  _  _    __   U  __  _ __ _
  /__\  (  )(  )(  )(  )( \( )  (  )    /__\( (  ) )        This message is 
 /(__)\  )(__\ () /  )(  )  (    )(__  /(__)\\  / /     brought to you from
(__)(__)(____)\__/  (__)(_)\_)  (____)(__)(__)\/\/      Motaba River Valley

Received on Fri May 19 1995 - 00:00:00 CEST