You can not cast direct from varchar2 to timestamp in your Teiid model, as there is no implicit conversion available. You would need to read as string in source model, then use parsetimestamp method see  and convert to timstamp in a view transformation.
In Source model
create foreign table Foo (strvalue varchar2);
In view model:
create view Bar AS select parsetimestamp(strvalue, 'YYYY-MM-DD HH24:MI:SS' ) as x From Source.Foo
the answer you provided is correct for the scenario where we do not have to populate any destination table . What will be case if i have a destination table and there i have defined a column as timestamp to populate the value m getting it from the query. More over if from that destination table if i want to read the data as timestamp will the Teiid support for that.
Yes, as long as you are using the View table (Bar from above) to populate your destination table then the column is of type "timestamp". When reading, you read as timestamp no conversion needed.
i have tried the above it did not work. when i tried
createview Bar AS (select parsetimestamp(cast(strvalue as date), 'YYYY-MM-DD HH24:MI:SS' ) as x From Source.Foo )
then it worked.
Thanks for the suggestion.