Setting the date format in the SQL * loader management file

Question

I have a csv file that has "September 17, 2009 11:06:06 AM" as the variable COMPLETED_ON

I am using the sql loader to load data into oracle using the following:

LOAD DATA INFILE 'c:/load/file_name.csv' APPEND INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( COMPLETED_ON DATE "not sure what to put here", ) 

in oracle I have this column created as follows:

"COMPLETED_ON" TIMESTAMP (0) with local time zone

How do I change the COMPLETED_ON date in a control file?

Answer 1

I'm not 100% with LOAD DATA INFILE syntax, but I know that the format string 'DD Month, YYYY HH:MI:SS AM' matches your date format. I was able to use it in TO_DATE to convert the selection date. Try the following:

 LOAD DATA INFILE 'c:/load/CW_COMPLIANCE.csv' APPEND INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( COMPLETED_ON DATE 'DD Month, YYYY HH:MI:SS AM', ) 

Answer 2

Thank you sooo much .... It helped me sooooo !!!!