Problem with invalid timestamp
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem with invalid timestamp

  1. #1
    Join Date
    Jan 2010
    Posts
    20

    Question Problem with invalid timestamp

    Hi I need to load the data from CSV file to oracle tablesusing sql loader. A coulmn CRE_TIME in the CSV file has the possibilty of having either a null value or 00:00:00 or a valid timestamp. But the column CRE_TIME is defined with data type INTEGER EXTERNAL.So i need to replace null or 00:00:00 with a value 0 in the oracle table.

    my control file looks like
    CRE_TIME "Decode(:CRE_TIME,null,replace(TO_DATE('00:00:00','HH24:MI:SS'),':',''),'00:00:00',replace(TO_DATE('0 0:00:00','HH24:MI:SS'),':',''),replace(TO_DATE(:CRE_TIME,'HH24:MI:SS'),':',''))",

    But still it is not working.(Error:ORA-01722: invalid number) Please advice on what could be done.

    Please note that the datatype for coulmn is INTEGER EXTERNAL

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Try this...
    Code:
    to_number(nvl((to_char(to_date(CRE_TIME,'hh24:mi:ss'),'hh24miss')),0))
    Here are the three cases you mentioned...
    Code:
    SQL> 
    SQL> select  to_number(nvl((to_char(to_date('00:00:00','hh24:mi:ss'),'hh24miss')),0))
      2  from    dual
      3  ;
    
    TO_NUMBER(NVL((TO_CHAR(TO_DATE('00:00:00','HH24:MI:SS'),'HH24MISS')),0))
    ------------------------------------------------------------------------
                                                                           0
    
    SQL> select  to_number(nvl((to_char(to_date(NULL,'hh24:mi:ss'),'hh24miss')),0))
      2  from    dual
      3  ;
    
    TO_NUMBER(NVL((TO_CHAR(TO_DATE(NULL,'HH24:MI:SS'),'HH24MISS')),0))
    ------------------------------------------------------------------
                                                                     0
    
    SQL> select  to_number(nvl((to_char(to_date('12:30:59','hh24:mi:ss'),'hh24miss')),0))
      2  from    dual
      3  ;
    
    TO_NUMBER(NVL((TO_CHAR(TO_DATE('12:30:59','HH24:MI:SS'),'HH24MISS')),0))
    ------------------------------------------------------------------------
                                                                      123059
    
    SQL>
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width