Re: Help Loading YYYYMMDD Dates

roywagner_at_aol.com (Roy Wagner) wrote:
>
> 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.
>
>

How about loading the field into a character field rather than a date field, then doing an update on the table to put it into a date field:

	CREATE TABLE testdate
	   	(testdate     DATE,
    		testchar     CHAR(10))

	------------
	rem TESTDATE.CTL

	LOAD DATA
	INFILE 'c:\testdate.dat'	
	REPLACE
	INTO TABLE testdate
   		(testchar   POSITION (1:8) CHAR(10))

then in sqlplus:

	update testdate
	set testdate = to_date(replace(testchar,' ','0'),'YYYYMMDD');

I hope this helps

Yours ever

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