This discussion is archived
3 Replies Latest reply: Jul 8, 2011 6:32 AM by Sadock RSS

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

Sadock Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points