Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> sybase date field to Oracle - sqlldr

sybase date field to Oracle - sqlldr

From: John Hallas <john.hallas_at_bjss.co.uk>
Date: Tue, 18 Sep 2007 11:16:54 +0100
Message-ID: <E02CB9B2777CF8459C86C49B48C48EC6024B5C2A@exchange.bjss.co.uk>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 18 2007 - 05:16:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US