OraFAQ Forum: Server Utilities » DATE CONVERSION

Home » RDBMS Server » Server Utilities » DATE CONVERSION - SQL LOADER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DATE CONVERSION - SQL LOADER [message #166875]

Mon, 10 April 2006 02:09

samas
Messages: 1
Registered: April 2006

Junior Member

I'm trying to upload the data using sql loader. All find except date. Did you have any suggestion to upload the twa.dat into twa table?

CREATE TABLE twa
(
cardnumber VARCHAR2(16),
startdatetime DATE,
enddatetime DATE,
bno VARCHAR2(22),
ano VARCHAR2(10
);

<twa.dat>
1000024114|Apr 6 2006 9:48:43:703AM|Apr 6 2006 9:53:05:703AM|067622711|046428770
1000071989|Apr 6 2006 12:00:22:816PM|Apr 6 2006 12:00:56:816PM|0355692002|073554930
1000071989|Apr 6 2006 12:01:56:170PM|Apr 6 2006 12:02:33:170PM|0378820688|073542252

load data
infile 'twa.dat'
append
into table twa
FIELDS TERMINATED BY "|"
(carnumber, startdatetime, enddatetime, bno, ano)

Report message to a moderator

Re: DATE CONVERSION - SQL LOADER [message #166894 is a reply to message #166875]

Mon, 10 April 2006 03:14

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You have to specify the date format. Eg:

load data
infile 'twa.dat'
append
into table twa
FIELDS TERMINATED BY "|"
(carnumber
, startdatetime DATE "Mon DD YYYY HH24:MI:SSAM"
, enddatetime DATE "Mon DD YYYY HH24:MI:SSAM"
, bno
, ano)

But since your dates have fractional seconds, you would have to use the TIMESTAMP datatype instead of DATE. (I haven't tried this - it may not work)...

CREATE TABLE twa 
(
cardnumber VARCHAR2(16), 
startdatetime TIMESTAMP, 
enddatetime TIMESTAMP, 
bno VARCHAR2(22), 
ano VARCHAR2(10
);

load data
infile 'twa.dat'
append
into table twa
FIELDS TERMINATED BY "|"
(carnumber
, startdatetime TIMESTAMP 3 "Mon DD YYYY HH24:MI:SS.SSSSSAM"
, enddatetime TIMESTAMP 3 "Mon DD YYYY HH24:MI:SS.SSSSSAM"
, bno
, ano)

Ross Leishman

Report message to a moderator

Previous Topic:	SQL*Loader-941
Next Topic:	Difference between Oracle 8 and 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 13 05:38:13 CST 2025