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

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>

---