 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)
|
|
|
|
| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
