Problem with date format

I have table with a column defined like
INSTATE_DATE DATE
I have to laod the data frm the csv file to this table. my csv file has data 00/00/0000. Even though it is an invalid date
I have load this date to my oracle table. I have an option of loading a default value say 01/01/2010 everytime i come across 00/00/0000.
I tried to modify the control file like
INSTATE_DATE DATE "replace(:INSTATE_DATE,'00/00/0000','01/01/2010')"
Even then im getting the error "ORA-01821: date format not recognized"
Please let me know if there are any other way to achive this.

Try...

Code:

---

INSTATE_DATE "decode(:INSTATE_DATE,'00/00/0000','01/01/2010',:INSTATE_DATE)"

---

... if still having datatype issues add to_date() function.

select
( case
:INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
return to_date('01/01/2010','DD-MM-YYYY');
else
return :INSTATE_DATE
end ) INSTATE_DATE
from table1

Quote:

---

Originally Posted by aryasen

select
( case
:INSTATE_DATE when to_date('00/00/0000','DD-MM-YYYY') then
return to_date('01/01/2010','DD-MM-YYYY');
else
return :INSTATE_DATE
end ) INSTATE_DATE
from table1

---

You didn't test it, don't you?
look...

Code:

---

SQL>
SQL> select to_date('00/00/0000','DD-MM-YYYY') from dual;
select to_date('00/00/0000','DD-MM-YYYY') from dual
              *
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month

---

I tried to_date also.. still its not working..
Im not sure whether im using to_date function properly.. please correct me if im wrong

INSTATE_DATE DATE "to_date (replace (replace (:REINSTATE_DATE, '/', ''), ' ', ''), 'mmddyyyy')"

Use decode() - look at second post.

Thanks. Its working fine with boundfiller solution.