Home » SQL & PL/SQL » SQL & PL/SQL » Help In Oracle SQL in converting GMT to EST with DST and Date Offset (Oracle 10)
Help In Oracle SQL in converting GMT to EST with DST and Date Offset [message #420955] Thu, 03 September 2009 08:36 Go to next message
Saul Mond
Messages: 2
Registered: August 2002
Junior Member
Hi, I have a query that does not seem to work trying to convert a date field that is in GMT to est and using extract(timezone_hour FROM TO_TIMESTAMP_TZ as an offsetr

HEre is my sql

dtl.start_dt_gmt + (extract(timezone_hour FROM TO_TIMESTAMP_TZ( dtl.start_dt_gmt,'DD-MON-YYYY HH24:MI:SS TZH:TZM'))/24 ) START_DT_Local

If the date (dtl.start_dt_gmt) is may 1 and gmt starts at 04:00 AM , the extract offset produces -4

However, if the date (dtl.start_dt_gmt) is Feb 1 which begins at 05:00 AM GMT, the date offset still gives 04. What am i doing wrong? Any help would be appreciated. Thanks.

Saul
Re: Help In Oracle SQL in converting GMT to EST with DST and Date Offset [message #420965 is a reply to message #420955] Thu, 03 September 2009 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Help In Oracle SQL in converting GMT to EST with DST and Date Offset [message #421042 is a reply to message #420955] Thu, 03 September 2009 16:32 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Saul Mond wrote on Thu, 03 September 2009 06:36
trying to convert a date field that is in GMT to est



SELECT t.ts gmt
      ,FROM_TZ(t.ts, 'GMT') AT TIME ZONE 'America/New_York' est
FROM (SELECT CAST(systimestamp AS TIMESTAMP) ts FROM dual) t;
Re: Help In Oracle SQL in converting GMT to EST with DST and Date Offset [message #421062 is a reply to message #421042] Fri, 04 September 2009 00:47 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe:
SQL> select new_time(sysdate,'gmt','est') from dual;
NEW_TIME(SYSDATE,'G
-------------------
04/09/2009 02:46:36

1 row selected.

Regards
Michel
Previous Topic: REF CURSOR with NVARCHAR2 data type
Next Topic: Sorting
Goto Forum:
  


Current Time: Fri Dec 09 02:09:14 CST 2016

Total time taken to generate the page: 0.07253 seconds