Help Loading YYYYMMDD Dates

From: Roy Wagner <roywagner_at_aol.com>
Date: 1995/05/17
Message-ID: <3pdgjc$msu_at_newsbf02.news.aol.com>#1/1

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                                                                 
                                                                          

---

• Roy Wagner - "When driving my Miata, I always get to where I'm going before I want to stop driving it."

Received on Wed May 17 1995 - 00:00:00 CEST