| Date format in control file [message #185584] |
Wed, 02 August 2006 07:53  |
Ramnath
Messages: 7
Registered: July 2006
|
Junior Member |
|
|
My input file has
12/31/2005|08:03:48|259123|0280|26555|CHG|333333333|AAA|0001|R|752104|2902
Oracle table need to be uplaoded as follows
ACTION_DATE, (Date)-->12/31/2005
ACTION_TIME, (Date)-->08:03:48
BANK_NUM, (Char)-->0280
DNIS, (char)-->26555
ACTION, (number)-->CHG
SSN_INQ, (number)-->333333333
CUST_NAME, (varchar2)-->AAA
ANI (number)-->752104
Control file
LOAD DATA
INFILE 'E:\1.txt'
BADFILE 'E:\1.bad'
DISCARDFILE 'E:\1.dsc'
APPEND
INTO TABLE "PIN_CHANGE" FIELDS TERMINATED BY '|'
( ACTION_DATE
,
ACTION_TIME
,
BANK_NUM
,
DNIS
,
ACTION
,
SSN_INQ
,
CUST_NAME
,
ANI
)
Query:
Oracle doesn't seems to support the date format mm/dd/yyyy directly.
Even incase of insert statement only the following works properly
insert into pin_change(ACTION_DATE) values (to_date('02/12/2006','dd/mm/yyyy'))
Ho wto implement this logic in control file
|
|
|
|
| Re: Date format in control file [message #185594 is a reply to message #185584] |
Wed, 02 August 2006 08:42  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Use:
.
.
.
action_date "to_date(:action_date,'mm/dd/yyyy')",
.
.
.
You do know of course that a DATE column in Oracle stores both date and time. It is poor design to store the date in one column and time component in another.
| Ramnath wrote on Wed, 02 August 2006 08:53 |
Oracle doesn't seems to support the date format mm/dd/yyyy directly.
Even incase of insert statement only the following works properly
insert into pin_change(ACTION_DATE) values (to_date('02/12/2006','dd/mm/yyyy'))
|
Ha, that's a good one. You showed what you are supposed to do. If you want to insert a DATE into a column, then yes, use a TO_DATE function. Did you think that you should insert a string such as '02/12/2006' into the column? Seems sloppy at best and incorrect at worst.
[Updated on: Wed, 02 August 2006 08:50] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
