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 10.2.0.3 - 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 '"'
(
CREDIT_TOKEN,
BUCKET_NUMBER,
LAST_RESET_TIME timestamp "mon dd yyyy hh:mi:ss AM"
)
Or
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
LAST_RESET_TIME.
ORA-01855: AM/A.M. or PM/P.M. required
TIA
John
+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.
http://www.bjss.co.uk
Other related posts:
