This discussion is archived
4 Replies Latest reply: Jan 30, 2013 6:13 AM by 976439 RSS

Control Data with Timestamp

976439 Newbie
Currently Being Moderated
Hi, i was looking for some help in regards to adding data into my tables. I have sucessfully managed to add my data within my tables but I have just come across an issue with time values.

The following is my control file.
OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1)
load data
infile 'c:/My Files/Daily Feeds/20130128/data_20130128.txt' "STR '\n'"

APPEND
 into table DATA_2
 WHEN (RID <> RID')
 fields terminated by "~|~" optionally enclosed by '"'
TRAILING NULLCOLS 
 ( 
     RID,
     SEQ,
     NAME,
     VALUE,
    CONVERTED_TIMESTAMP                    timestamp 'DD-MON-RR HH:MI:SS.FF3 PM'
 )
The problem lies within the value column as the data within this column varies from time values, names, other ids. The problem is when coming across a time value the hh:mm are being entered correctly but for some reason the seconds get rounded of to 00.

The text file shows the following (example):
rid~|~seq~|~name~|~value~|~converted_timestamp
0VPH5V2GQ15W29QE~|~71~|~str_TimestampDelivery~|~2012-12-01 08:30:58~|~01-DEC-12 08.30.58.000 AM
but the database shows this as:
0VPH5V2GQ15W29QE        71     str_TimestampDelivery     01/12/2012 08:30:00     01-DEC-12 08:30:00
Other note to bear in mind, is the next row of data will not show time values hence the datatype in the control file has been left blank.

Any advice would be really appreciated.

Edited by: 973436 on 29-Jan-2013 06:51

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points