convert Date time TZ to Date Time

[chan11]

I would like to know how to convert 24-FEB-12 03.16.05.941105000 AM -06:00 to DD-MON-YYYY HH:MI EST format.

Thanks.

[Tim...]

Hi.

First we need to convert your string to a date;

Code: Select all

TO_TIMESTAMP_TZ('24-FEB-12 03.16.05.941105000 AM -06:00', 'DD-MON-RR HH.MI.SS.FF9 PM TZH:TZM')

Next we need to convert the date to a new timezone.

Code: Select all

new_time(dateval, 'GMT', 'EST')

Next we need to converts the date into a string in the correct format.

Code: Select all

TO_CHAR(dateval, 'DD-MON-YYYY HH:MI')

Putting it all together.

Code: Select all

SELECT TO_CHAR(new_time(myval, 'GMT', 'EST'), 'DD-MON-YYYY HH:MI')
FROM   (SELECT TO_TIMESTAMP_TZ('24-FEB-12 03.16.05.941105000 AM -06:00',
                               'DD-MON-RR HH.MI.SS.FF9 PM TZH:TZM') AS myval
        FROM DUAL);

Cheers

Tim...