Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!newsfeed.bahnhof.se!195.197.54.117.MISMATCH!feeder1.news.jippii.net!nntp.inet.fi!central1.inet.fi!inet.fi!read3.inet.fi.POSTED!53ab2750!not-for-mail From: "Jack" Newsgroups: comp.databases.oracle.server References: <1113737110.301348.208030@o13g2000cwo.googlegroups.com> <1113758061.224694@yasure> <1113796238.518823.18430@l41g2000cwc.googlegroups.com> Subject: Re: Problem with Oracle Timezones Lines: 138 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-RFC2646: Format=Flowed; Original Message-ID: Date: Mon, 18 Apr 2005 09:45:44 GMT NNTP-Posting-Host: 194.252.147.254 X-Complaints-To: abuse@inet.fi X-Trace: read3.inet.fi 1113817544 194.252.147.254 (Mon, 18 Apr 2005 12:45:44 EEST) NNTP-Posting-Date: Mon, 18 Apr 2005 12:45:44 EEST Organization: Sonera corp Internet services Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:240644 wrote in message news:1113796238.518823.18430@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@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 --------------------------------------------------------------------------- 10-FEB-05 10.22.30.000000 AM EUROPE/LONDON SQL> Jack