how to convert timestamp with timezone to only timestamp while using sql loader

Question

enter code here

       load data
         infile '/u01/app/oracle/scripts/Elite_export.csv'
         discardfile '/u01/app/oracle/scripts/data.dis'
         into table TBLRADIUSCUSTOMER_TEST
         fields terminated by ","
        ( 
USERNAME ,
PASSWORD ,
CUSTOMERSTATUS ,
CONCURRENTLOGINPOLICY ,
RADIUSPOLICY ,
ADDITIONALPOLICY ,
PARAM1 ,
PARAM2 ,
PARAM3 ,
PARAM4 ,
CUSTOMERTYPE ,
CALLINGSTATIONID ,
CUI ,
MACVALIDATION ,
IMSI ,
MEID ,
MSISDN ,
MDN ,
GEOLOCATION ,
CREATEDATE  "TO_TIMESTAMP(:CREATEDATE,'DD-MM-YY HH24:MI:SS.ff')",
LASTMODIFIEDDATE "TO_TIMESTAMP(:LASTMODIFIEDDATE,'DD-MM-YY HH24:MI:SS.ff')"
  )

the error i am facing is given below :

enter code here

     SQL*Loader: Release 11.2.0.3.0 - Production on Mon Mar 17 12:08:44 2014

      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

       Control File:   data.ctl
       Data File:      /u01/app/oracle/scripts/Elite_export.csv
      Bad File:     Elite_export.bad
      Discard File: /u01/app/oracle/scripts/data.dis 
      (Allow all discards)

      Number to load: ALL
      Number to skip: 0
      Errors allowed: 50
      Bind array:     64 rows, maximum of 256000 bytes
      Continuation:    none specified
      Path used:      Conventional

      Table TBLRADIUSCUSTOMER_TEST, loaded from every logical record.
      Insert option in effect for this table: INSERT

      Column Name                  Position   Len  Term Encl Datatype
       ------------------------------ ---------- ----- ---- ---- ---------------------
      USERNAME                            FIRST     *   ,       CHARACTER            
      PASSWORD                             NEXT     *   ,       CHARACTER            
      CUSTOMERSTATUS                       NEXT     *   ,       CHARACTER            
      CONCURRENTLOGINPOLICY                NEXT     *   ,       CHARACTER            
      RADIUSPOLICY                         NEXT     *   ,       CHARACTER            
      ADDITIONALPOLICY                     NEXT     *   ,       CHARACTER            
      PARAM1                               NEXT     *   ,       CHARACTER            
      PARAM2                               NEXT     *   ,       CHARACTER            
      PARAM3                               NEXT     *   ,       CHARACTER            
      PARAM4                               NEXT     *   ,       CHARACTER            
      CUSTOMERTYPE                         NEXT     *   ,       CHARACTER            
      CALLINGSTATIONID                     NEXT     *   ,       CHARACTER            
      CUI                                  NEXT     *   ,       CHARACTER            
      MACVALIDATION                        NEXT     *   ,       CHARACTER            
      IMSI                                 NEXT     *   ,       CHARACTER            
      MEID                                 NEXT     *   ,       CHARACTER            
      MSISDN                               NEXT     *   ,       CHARACTER            
      MDN                                  NEXT     *   ,       CHARACTER            
     GEOLOCATION                          NEXT     *   ,       CHARACTER            
     CREATEDATE                           NEXT     *   ,       CHARACTER            
           SQL string for column : "TO_TIMESTAMP(:CREATEDATE,'DD-MM-YY HH24:MI:SS.ff')"
     LASTMODIFIEDDATE                     NEXT     *   ,       CHARACTER            
      SQL string for column : "TO_TIMESTAMP(:LASTMODIFIEDDATE,'DD-MM-YY HH24:MI:SS.ff')"

     value used for ROWS parameter changed from 64 to 47
     Record 1: Rejected - Error on table TBLRADIUSCUSTOMER_TEST, column CREATEDATE.
     ORA-01830: date format picture ends before converting entire input string

     Record 2: Rejected - Error on table TBLRADIUSCUSTOMER_TEST, column CREATEDATE.
      ORA-01830: date format picture ends before converting entire input string


     and so on.....

need help while using SQL Loader to change datatype from TIMESTAMP with Zone to TIMESTAMP(6)

please suggest me how to discard timezone detail while loading data in to the Oracle table

score 0 · Answer 1 · answered Mar 17 '14 at 17:18

if you don't care about the timestamp, and just want the numbers from the date, discarding timezone information, use substr to discard time zone info:

CREATEDATE  "TO_TIMESTAMP(SUBSTR(instr(:CREATEDATE, ' ', 10),1,24),'DD-MM-YY HH24:MI:SS.ff')",

Substr to get the date part in format 'DD-MM-YY HH24:MI:SS.ff'

instr(:CREATEDATE, ' ', 17)

Position of separator between the date and timezone info. I assumed there will be a space between the numbers and tz info, so i used 17 as starting position for instr to find first space after the 'DD-MM-YY HH24:MI:SS' date part.

thanks for valuable response, i used simple SUBSTR function to achive the same : [link](http://example.com) _italic_ **bold** `CREATEDATE "substr(:CREATEDATE,1,31)" ,` — user3185303, Mar 18 '14 at 07:23

how to convert timestamp with timezone to only timestamp while using sql loader

1 Answers1