using translate for date in the control file !!

Hi,

I am having data file where in i expect date but get non-numeric character.

I am generating control file dynamically (programatically).

While creating control file i know that here the type of data will be date so i put datatype as date. But at one place i got non numeric character instead of date.

Now i want to put somekind of logic built in my dynamic generation which will check whether if value within that column is character then make it null and then load data. And if it is numeric keep it as it it.
/*
i.e.
if(non-numericChars)
make value null before loading.
else
keep the value as it and then load it.
*/

I heard that with translate function we can do that. Right ?

So if i have a line like this in my control file:

column_Name date "MM/DD/YYYY HH:MI:SS AM"

How should i write the above statement to put the above mentioned logic.

Any help would be highly appreciated.

Regards,
Dipesh

Hi,

I got it my problem solved.
Actually i was thinking that i will have to check the presence of character and then do translation but i am able to do the translation writing generic translation like this.

"ColName Date "MM/DD/YYYY HH:MM:SS AM" Translate('ColName','abcdefghijklmnopqrstuvwxywABCDEFGHIJKLMNOPQRSTUVWXYZ','')"

So whenever there are some characters instead of date they are replaced by null.

Thanks !!

Regards
Dipesh