How to cast varchar into timestamp in postgres

[krithikas]

I have a table (which i cannot modify) where date and time fields are stored as VARCHAR. But i have to cast these fields into timestamp.

My requirement is like,
timestamp (select date, time where max(timestamp(d ate,time));

How to do this in postgres?

[michaelb]

I'm not sure if you can really cast varchar into timestamp, (although I don't have the latest version of Postgres, which may have a larger set of cast operators), but you can try something like this instead:

select (mydate || ' ' || mytime) from foo where (mydate || ' ' || mytime) = (select max (mydate || ' ' || mytime) from foo);

[krithikas]

Thank you,
I found a function to_timestamp and it casts string into timestamp
I tried sth like ,

to_timestamp('0 1 Jan 2007 09:09:09', 'DD Mon YYYY HH:MI:SS')

It works .

[michaelb]

Great, thanks for letting me know.
michael