Home » RDBMS Server » Server Utilities » Date format in control file
Date format in control file [message #185584] Wed, 02 August 2006 07:53 Go to next message
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 Go to previous message
joy_division
Messages: 4640
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

Previous Topic: problem with sql loader
Next Topic: downgrade oracle9i to oracle8i
Goto Forum:
  


Current Time: Fri Dec 02 14:13:58 CST 2016

Total time taken to generate the page: 0.10989 seconds