Problem with date format

[anijan]

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.

[PAVB]

Try...

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

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

[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

[PAVB]

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...

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

[anijan]

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')"

[PAVB]

Use decode() - look at second post.

[anijan]

Thanks. Its working fine with boundfiller solution.