1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL and GMT AND TO_TIMESTAMP_TZ not working

Discussion in 'SQL PL/SQL' started by smond, Sep 3, 2009.

  1. smond

    smond Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Oracle SQL HELP with convert GMT to EST and DST and timezone_hour FROM TO_TIMESTAMP_TZ
    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
     
  2. sameer

    sameer Forum Advisor

    Messages:
    105
    Likes Received:
    6
    Trophy Points:
    240
    Your "sql" looks totally cryptic to me, perhaps you should post the proper query.
     
  3. smond

    smond Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a Oracle Date column start_dt_gmt that stores a date in GMT time like 04:00 AM if May 1 or 05:00 AM if Feb 1 . I want select that column and use a date offset for EST so the value of the date will be 12:00 AM for May 1 or 12:00 AM for Feb 1 . However, I tried adding " + (extract(timezone_hour FROM TO_TIMESTAMP_TZ( start_dt_gmt,'DD-MON-YYYY HH24:MI:SS TZH:TZM'))/24 ) " and it works fine may 1 but not feb 1. Adding the date offset convert may 1 gmt time to 12:00 AM and for feb 1 , it convert to 1:00 AM which is wrong