Home » RDBMS Server » Server Utilities » insert only date using sqlldr (oracle 9i)
insert only date using sqlldr [message #457177] |
Fri, 21 May 2010 09:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vijay123
Messages: 3 Registered: May 2010
|
Junior Member |
|
|
Hi,
I m new to oracle. we are using sqlldr to load data into table.we have data in a csv file as below
"TXN_DATE","TXN_HOUR","VID","HID"
"2010-05-18 20:00:00.0","20","184","212"
"2010-05-18 21:00:00.0","21","184","212"
"2010-05-19 17:00:00.0","17","184","212"
I just want to insert only date without timestamp from the first field TXN_DATE. i.e., i just want 2010-05-18 in my table column.
my table desc is
Name Null? Type
----------------------------------------- -------- ---------
SEQID NUMBER(5)
TXN_DATE NOT NULL DATE
TXN_HOUR NOT NULL NUMBER(2)
VID NOT NULL NUMBER(5)
HID NOT NULL NUMBER(5)
i tried many combination but i couldn't achieve. right now i am able to get only the complete date with timestamp using the following control file.
APPEND INTO PERF_STATS
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
TRAILING NULLCOLS
(
SEQID SEQUENCE(MAX,1),
TXN_DATE timestamp 'yyyy-mm-dd hh24:mi:ss.ff3',
TXN_HOUR integer external,
VID integer external,
HID integer external,
}
Please help me on how to get this date alone from that field.
Thanks,
Vijay
|
|
|
|
Re: insert only date using sqlldr [message #457181 is a reply to message #457178] |
Fri, 21 May 2010 10:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vijay123
Messages: 3 Registered: May 2010
|
Junior Member |
|
|
Hi Black Swan,
Sorry for not providing enough details. This is my first post. I will follow your guidelines in future.
I have tried this in my ctl file and it works fine for me.
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",
Thanks,
Vijay
|
|
|
|
Re: insert only date using sqlldr [message #457183 is a reply to message #457181] |
Fri, 21 May 2010 10:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
What is the problem with what you posted?
SQL> select * from PERF_STATS ;
no rows selected
SQL> host type t.ctl
load data
infile *
APPEND INTO TABLE PERF_STATS
FIELDS TERMINATED BY ',' optionally ENCLOSED BY '"'
TRAILING NULLCOLS
(
SEQID SEQUENCE(MAX,1),
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",
TXN_HOUR integer external,
VID integer external,
HID integer external
)
begindata
"2010-05-18 20:00:00.0","20","184","212"
"2010-05-18 21:00:00.0","21","184","212"
"2010-05-19 17:00:00.0","17","184","212"
SQL> host sqlldr michel/michel control=t.ctl
SQL*Loader: Release 10.2.0.4.0 - Production on Ven. Mai 21 17:18:13 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Commit point reached - logical record count 3
SQL> select * from PERF_STATS ;
SEQID TXN_DATE TXN_HOUR VID HID
---------- ------------------- ---------- ---------- ----------
1 18/05/2010 00:00:00 20 184 212
2 18/05/2010 00:00:00 21 184 212
3 19/05/2010 00:00:00 17 184 212
3 rows selected.
It works for me.
Regards
Michel
|
|
|
Re: insert only date using sqlldr [message #457196 is a reply to message #457183] |
Fri, 21 May 2010 14:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vijay123
Messages: 3 Registered: May 2010
|
Junior Member |
|
|
Hi Michel,
My requirement is to get only the date without timestamp into my table for txn_date column.
Previously i tried with following statement in ym control file.
TXN_DATE timestamp 'yyyy-mm-dd hh24:mi:ss.ff3'
It brings the date with timestamp.
Then i tried with the following statement.
TXN_DATE "to_date(SUBSTR(:txn_date,1,10),'yyyy-mm-dd')",
It inserts only date into the table.
Thanks,
Vijay
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:20:02 CDT 2024
|