4 Replies Latest reply: Jan 30, 2013 8:13 AM by 976439 RSS

    Control Data with Timestamp

    976439
      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