convert Date time TZ to Date Time

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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.
chan11
Member
 
Posts: 23
Joined: Wed Aug 10, 2011 5:59 pm

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


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest