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 it to TIMESTAMP as it is. What is it 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.

To do this, there must be a format_string format.

Please help as I tried a lot of things, but nobody works. :(

I am using Oracle 11gR2.

Thanks.

Answer 1

DATE and TIMESTAMP are two different data types. Therefore, you need to use the correct conversion function for each, which in your case will be TO_TIMESTAMP () .

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

Answer 2

 select from_tz(to_timestamp('14-SEP-11 12.33.48.537150 PM', 'DD-Mon-RR HH.MI.SS.FF AM'), 'EUROPE/LONDON') from dual