Oracle - ora-01841 to date

Hola ,que tal todo?
os queria pedir un pequeño consejo... llevo intntando realizar una tarea unos dias y la verdad es que me da un fallo a la hora de realizar un cast.

La cosa es que tengo una tabla. Con 5 campos, todos son string(el to number del final funciona, el fallo esta en el to_date)... y necesito crear otra tabla que modifique algunos de esos campos que son string a date. Pero hay algunos valores de fecha nulos , por ejemplo año 0000 o mes 00 o dia 00. por lo cual tengo este codigo.


create table tmp_mibebeyyo_core
as
select
cod_id,
cod_zipcode,
str_email,

to_date((case when (FEC_BIRTHDAYMOM) like '0000%' then null when (FEC_BIRTHDAYMOM) like '%-00%' then null else (FEC_BIRTHDAYMOM) end ),'YYYY-MM-DD')as FEC_BIRTHDAYMOM ,
to_date((case when (fec_birthdaybaby) like '0000-%' then '1990-10-01' when (fec_birthdaybaby) like '%-00%' then '1990-10-01' else (fec_birthdaybaby) end),'YYYY-MM-DD') as FEC_BIRTHDAYBABY ,

to_number(substr(md5(cast(cod_id || cod_zipcode || str_email || fec_birthdaymom || fec_birthdaybaby as varchar2(32))||'mibebeyyo'),10,16),
'xxxxxxxxxxxxxxxx') as cod_product

from
tmp_mibebeyyo;


El problema esta en el to_date... que parece ser que hace el cast antes de poner el valor nulo y me dice que el año 0000 no es valido (Error SQL: ORA-01841: el valor (completo) del año debe estar entre -4713 y +9999, y no debe ser igual a 0)


Puse que en vez de null fuera una fecha... pero no, sigue persistiendo el mismo error.... Algún consejo?

Muchas gracias.

A ver yo es que pondría el TO_DATE al final... mira

Espero te sirva...

Saludos
Pd. Si te sirve la info a mi me sirve un +1

ME sirve, pero ahora me da un error de mes para fecha incorrecta, La idea es que cuando encuentra una fecha incorrecta me lo ponga a null o a una valida... se puede poner en la query un if... fecha-incorrecta= null y si no es incorrecta que haga el cast a date?


edit : PD muchas gracias

Pues mira quizás lo mas fácil seria hacer una función ...

Algo como esto:


De tal modo que tu query quedaría...


Saludos....
Pd. Si te sirve la info a mi me sirve un +1

mil gracias