Convert VARCHAR2 to TIMESTAMP in Oracle

Question

I have a VARCHAR2 value in the format '14-SEP-11 12.33.48.537150 AM' and I need to convert this to a TIMESTAMP as is. That is like,

SELECT TO_DATE('14-SEP-11 12.33.48.537150 AM', '<format_string>') FROM DUAL;

I want the return from the above query to be '14-SEP-11 12.33.48.537150 AM' in TIMESTAMP data format.

What should be the 'format_string' for this.

Please help since I tried many things but none works.. :(

I am using Oracle 11gR2.

Thanks.

Answer 1

DATE and TIMESTAMP are two different datatypes. So you need to use the correct conversion function for each, which in your case would be TO_TIMESTAMP().

SELECT TO_TIMESTAMP('14-SEP-11 12.33.48.537150 AM', 'DD-MON-RR HH:MI:SS.FF AM') 
FROM DUAL;