3 Replies Latest reply: Jul 8, 2011 8:32 AM by Sadock RSS

    new_time( date, zone1, zone2 ) to convert datetime zones

    Sadock
      Dear all

      I want to convert the datetime '07-JUL-2011 20:00' from 'Africa/Nairobi' time zone to 'Europe/London' time zone. However the following query throw back error ORA-01857: not a valid time zone

      SQL> select to_char(new_time(to_date('07-JUL-2011 20:00', 'DD-MON-YYYY HH24:MI'), 'Africa/Nairobi', 'Europe/London'), 'DD-MON-YYYY HH24:MI') from dual;

      I tried to guery 'V$TIMEZONE_NAMES' to get the abbreviation for 'Africa/Nairobi' and 'Europe/London' timeze so that i can use in place of zone1, and zone2 as follow but still it throw the same error

      select *from V$TIMEZONE_NAMES where tzname in ('Africa/Nairobi', 'Europe/London');

      Africa/Nairobi     LMT
      Africa/Nairobi     EAT
      Africa/Nairobi     BEAT
      Africa/Nairobi     BEAUT
      Europe/London     LMT
      Europe/London     GMT
      Europe/London     BST
      Europe/London     BDST

      SQL>select to_char(new_time(to_date('07-JUL-2011 20:00', 'DD-MON-YYYY HH24:MI'), 'EAT', 'GMT'), 'DD-MON-YYYY HH24:MI') from dual;

      ORA-01857: not a valid time zone

      My question is,what is the correct parameter to pass in zone1 and zone2 for each time zone location such as 'Africa/Nairobi', and 'Europe/London' to convert the time zone?

      I wonder why this one is working?

      SQL> select to_char(new_time(to_date('07-JUL-2011 20:00', 'DD-MON-YYYY HH24:MI'), 'EST', 'PST'), 'DD-MON-YYYY HH24:MI') from dual;

      TO_CHAR(NEW_TIME(
      -----------------
      07-JUL-2011 17:00

      Please any one for help

      Regard
      sadock
        • 1. Re: new_time( date, zone1, zone2 ) to convert datetime zones
          647939
          HI,

          Allowed timezones for the new_time funtion are given at below site of oracle documentation..

          http://download.oracle.com/docs/cd/B19306_01/olap.102/b14346/dml_x_monitor016.htm

          The timezones you are entering out of the list, causing error..

          Regards,
          Dipali.
          • 2. Re: new_time( date, zone1, zone2 ) to convert datetime zones
            32685
            Hello

            From the docs

            http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions092.htm


            Note:

            This function takes as input only a limited number of time zones. You can have access to a much greater number of time zones by combining the FROM_TZ function and the datetime expression. See FROM_TZ and the example for "Datetime Expressions".
            The arguments timezone1 and timezone2 can be any of these text strings:

            AST, ADT: Atlantic Standard or Daylight Time

            BST, BDT: Bering Standard or Daylight Time

            CST, CDT: Central Standard or Daylight Time

            EST, EDT: Eastern Standard or Daylight Time

            GMT: Greenwich Mean Time

            HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.

            MST, MDT: Mountain Standard or Daylight Time

            NST: Newfoundland Standard Time

            PST, PDT: Pacific Standard or Daylight Time

            YST, YDT: Yukon Standard or Daylight Time



            You might be better to use the from_tz function or
            select TO_TIMESTAMP_TZ('07-JUL-2011 20:00 Africa/Nairobi', 'DD-MON-YYYY HH24:MI TZR') at time zone 'Europe/London'from dual;
            If you need it as a date data type after that, you can cast it back...
            select CAST(TO_TIMESTAMP_TZ('07-JUL-2011 20:00 Africa/Nairobi', 'DD-MON-YYYY HH24:MI TZR') at time zone 'Europe/London' AS DATE) from dual;
            HTH

            David
            • 3. Re: new_time( date, zone1, zone2 ) to convert datetime zones
              Sadock
              Dear Bravid and Dipali Vithalani

              Thanks for your useful help. I was not ware that new_time has a limited number of time zones it can accept, but am wondering why oracle did this?

              Bravid thanks for your alternative solution, it do work for me, let me implement in my application

              Regards
              Sadock