SQLLDR :- Date format not recognized
JCApr 27 2007 — edited Apr 27 2007Hi
this is related again to data load through sqlldr process...
My data in flat file looks something like this
...
...
SLT433CR|~|N|~|34C|~|10/10/2002 12:26:09 PM|~|U06931
SNSM955TN|~|TK|~|63C|~|12/9/2005 2:21:19 PM|~|U14947
SVS2800KN|~|KB|~|48Y|~|1/14/2003 3:29:18 PM|~|U06931
SAB9KLCT|~|SB|~|47Q|~|9/30/2002 3:37:40 PM|~|U06931
...
...
In control file I have mentioned as
LOAD DATA
INFILE
"/oradata01/oesdb/gpstest/datafeeds/inbound/test1.txt"
REPLACE INTO TABLE BASE_SYSTEM
FIELDS TERMINATED BY '|~|' OPTIONALLY ENCLOSED BY '"'
(
BASE_SYSTEM_CODE CHAR ,
CHASSIS_ID CHAR ,
PROCESSOR_CODE CHAR ,
UPDATE_DATE DATE "TO_DATE(TO_CHAR(TO_DATE(:UPDATE_DATE,'MM/DD/YYYY HH:MI:SS AM or PM'),'DD-MON-YY'))",
,
UPDATE_UID CHAR "REPLACE(:UPDATE_UID,chr(13),'')"
)
But I am getting an error as
ORA-01821: Date format not recognized
I need the date in dd-mon-and yy format only
Any method of formating the date column particularly before pushing to database without any error.
regards
jc