I get this question from oracle-l groups about how to put "Sep 17 2007 7:00:00:020AM" (values from Sybase database) into an Oracle date column.. After several retries, I am be able to load it now. I create a text file (data.txt) contains one row.
Sep 17 2007 7:00:00:020AM
And I create two tables as following.
CREATE TABLE T_TIMESTAMP(COL1 TIMESTAMP);
CREATE TABLE T_DATE (COL1 DATE);
With the following SQL loader control file, I am able to load the rows into table "T_TIMESTAMP".
LOAD DATA
INFILE 'data.txt' "STR X'0a'"
INTO TABLE T_TIMESTAMP
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
COL1 TIMESTAMP "Mon dd YYYY hh:mi:ss:ffAM"
)
With the same SQL loader control file except the change of the table name, I load the data into table "T_DATE" by losing the millisecond value.