sybase date field to Oracle - sqlldr

  • From: "John Hallas" <john.hallas@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 18 Sep 2007 11:16:54 +0100

Oracle  - Solaris 

I am trying to load dump files from a Sybase database and I am
struggling over the date columns. The flat files have a date format of
"Sep 17 2007  7:00:00:020AM"


I do not need to use a time zone in Oracle so would prefer to load into
a table with the column defined as datatype date (however timestamp is
acceptable if that is the only way).


I am using sqlloader to load the data and I have tried many permutations
of the date format based around the two examples below


load data

infile 'tbl_auto_reset.csv'

into table tbl_auto_reset

fields terminated by ',' optionally enclosed by '"'





   LAST_RESET_TIME timestamp "mon dd yyyy hh:mi:ss AM"





LAST_RESET_TIME date "mon dd yyyy hh:mi:ss AM" (with the table created
with a different datatype (date/timestamp)


I know I could edit each file and remove some of the data field but I
would prefer to do it correct it via sqlloader


The error message is 


Record 26: Rejected - Error on table TBL_AUTO_RESET, column

ORA-01855: AM/A.M. or PM/P.M. required







+44 (0)113 223 2274 (direct)

+44 (0)113 297 9797


BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
Registered in England with company number 2777575.

Other related posts: