8 Replies Latest reply: Mar 5, 2008 11:19 AM by 580861 RSS

    convert GMT to EST

    447702
      Hi

      I need to convert GMT to EST in SQL . Please let me know , I was using sysdate initially , now I have to convert sysdate to EST as its in GMT for me.
        • 1. Re: convert GMT to EST
          181444
          Look at the following example:

          --
          -- Convert between time zones
          --
          select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as "Eastern Daylight"
          ,to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD HH24:MI:SS') GMT
          from sys.dual
          ;

          HTH -- Mark D Powell --
          • 2. Re: convert GMT to EST
            447702
            Thanks a lot
            • 3. Re: convert GMT to EST
              580861
              HOW Can we convert from Europe/Rome time to US central time (CDT)?

              It seems NEW_TIME does not support CET zone.

              Thank you
              • 4. Re: convert GMT to EST
                sgalaxy
                Here is an example:
                SELECT FROM_TZ(CAST(TO_DATE(sysdate, 'dd/mm/yyyy HH:MI:SS') AS TIMESTAMP), 'Europe/Athens')
                   AT TIME ZONE 'Europe/London' "London Local Time"
                FROM DUAL;
                You can find a timezone name querying the tzname column from v$timezone_names. For CDT ..is 'US/Michigan' .... isn't it..????

                Be careful....
                In the above query time has not been defined...so Oracle takes the midnight as time(local time)).

                If you want to define a specific time then:
                SQL> SELECT FROM_TZ(CAST(TO_DATE('05/03/2008 17:50:40', 'dd/mm/yyyy HH24:MI:SS') AS TIMESTAMP), 'Europe/Rome')
                  2     AT TIME ZONE 'US/Michigan' "London Local Time"
                  3   FROM DUAL;

                London Local Time
                --------------------------------------------------------------------------------
                05/03/08 11:50:40,000000 US/MICHIGAN
                Greetings...
                Sim

                Message was edited by:
                sgalaxy
                • 5. Re: convert GMT to EST
                  580861
                  Thank you.
                  So I use following query to get current Rome Time.
                  SELECT FROM_TZ(CAST(TO_DATE('05/03/2008 11:05:40', 'dd/mm/yyyy HH24:MI:SS') AS TIMESTAMP), 'Europe/Rome')
                  AT TIME ZONE 'US/Central' "Rome Local Time"
                  FROM DUAL;


                  Is there a way I can plug in SYSDATE instead of hard code datetime?
                  I tried couple of ways, not luck :(.
                  • 6. Re: convert GMT to EST - I got it
                    580861
                    IT should be

                    SELECT FROM_TZ(CAST(TO_DATE(SYSDATE, 'dd/mm/yyyy HH24:MI:SS') as TIMESTAMP), 'US/Central')
                    AT TIME ZONE 'Europe/Rome' "Rome Local Time"
                    FROM DUAL;
                    • 7. Re: convert GMT to EST
                      sgalaxy
                      "So I use following query to get current Rome Time.
                      SELECT FROM_TZ(CAST(TO_DATE('05/03/2008 11:05:40', 'dd/mm/yyyy HH24:MI:SS') AS TIMESTAMP), 'Europe/Rome')
                      AT TIME ZONE 'US/Central' "Rome Local Time"
                      FROM DUAL;
                      "

                      No...Using the above query you transform the Rome's Local Time to US/Central's Local Time.....

                      Sim
                      • 8. Re: convert GMT to EST - I got it
                        580861
                        SELECT FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'US/Central') at time zone 'Europe/Rome' "Current Europe Rome Italy Time"
                        FROM DUAL;


                        Is correct


                        IT should be

                        SELECT FROM_TZ(CAST(TO_DATE(SYSDATE, 'dd/mm/yyyy
                        HH24:MI:SS') as TIMESTAMP), 'US/Central')
                        AT TIME ZONE 'Europe/Rome' "Rome Local Time"
                        FROM DUAL;