Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with Oracle Timezones
<fitzjarrell_at_cox.net> wrote in message
news:1113796238.518823.18430_at_l41g2000cwc.googlegroups.com...
>
> DA Morgan wrote:
>> Tejaswini wrote:
>>
>> > Hi,
>> >
>> > I am getting some problems while using oracle time zones. The query
> I
>> > am
>> > using is as:
>> >
>> > select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
> HH:MI:SS
>> > AM')
>> > AS TIMESTAMP), 'GMT') at time zone 'Europe/Kiev' AS New_Time from
> dual;
>> >
>> > It gives me error as :
>> > ORA-01882: timezone region %s not found
>> >
>> > I checked that v$timezone_names contains entry for Europe/Kiev.
>> >
>> > The query works fine for some values but not for all values from
>> > v$timezone_names.
>> >
>> > What can be the reason for this error? Do I need to do any other
>> > setting?
>> >
>> > Thanks in advance.
>> > Tejaswini.
>>
>> SQL*Plus: Release 10.1.0.4.0 - Production on Sun Apr 17 10:17:26 2005
>>
>> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>>
>> Connected to:
>> Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 -
> Production
>> With the Partitioning, OLAP and Data Mining options
>>
>> SQL> select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
> HH:MI:SS
>> 2 AM')
>> 3 AS TIMESTAMP), 'GMT') at time zone 'Europe/Kiev' AS New_Time
> from
>> dual;
>>
>> NEW_TIME
>>
> ---------------------------------------------------------------------------
>> 10-FEB-05 12.22.30.000000 PM EUROPE/KIEV
>>
>> SQL>
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu
>> (replace 'x' with 'u' to respond)
>
> Works on 9.2.0.6, running on Solaris 8 64-bit, as well:
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Apr 18 03:35:11 2005
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> SQL> select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
> HH:MI:SSAM')
> 2 AS TIMESTAMP), 'GMT') at time zone 'Europe/Kiev' AS New_Time from
> dual
> 3 /
>
> NEW_TIME
> ---------------------------------------------------------------------------
> 10-FEB-05 12.22.30.000000 PM EUROPE/KIEV
>
> SQL>
>
> Of course the OP never stated which release of Oracle, and never
> bothered to state which operating system he's using. Also, no mention
> of the valid timezone values he's found. Until he posts more
> information we've provided the best response we can.
>
>
> David Fitzjarrell
>
Hi!
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 18 12:43:44 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production
SQL> select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
2 HH:MI:SSAM')
3 AS TIMESTAMP), 'GMT') at time zone 'Europe/Kiev' AS New_Time from
4 dual
5 /
select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
*
ERROR at line 1:
ORA-01882: timezone region not found
SQL>
SQL> SELECT * FROM V$TIMEZONE_NAMES where TZNAME='Europe/Kiev';
no rows selected
SQL>
SQL> select FROM_TZ(CAST(TO_DATE('10/2/2005 10:22:30','DD/MM/YYYY
2 HH:MI:SSAM')
3 AS TIMESTAMP), 'GMT') at time zone 'Europe/London' AS New_Time from
4 dual
5 /
NEW_TIME