This forum is currently locked. You can't register or post questions at this time. (read more)

convert Date time TZ to Date Time

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

chan11
Member
Posts: 23
Joined: Wed Aug 10, 2011 5:59 pm

convert Date time TZ to Date Time

Postby chan11 » Fri Sep 07, 2012 12:11 pm

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: convert Date time TZ to Date Time

Postby Tim... » Sat Sep 08, 2012 2:41 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron