RE: How to get timezone in 9i
- From: "Hollis, Les" <Les.Hollis@xxxxxx>
- To: <karai.ramesh@xxxxxxxxx>, <Christian.Antognini@xxxxxxxxxxxx>
- Date: Mon, 21 Feb 2005 19:56:44 -0600
I had to change the dbtimezone on one of my 9i DB's about 3 hours ago.
I did
Alter database set time_zone=3D'America/New_York';
To get it to show when doing=20
Select dbtimezone from dual;
I had to reboot the database...only then it actually change from
America/Chicago to America/New_York
Select * from v$timezone_names where tzname like 'America/%'; will
give you all of the timezones and their abbreviations
select * from v$timezone_names where tzname =3D 'America/New_York'
TZNAME TZABBREV
------------------------- --------
America/New_York LMT
America/New_York EST
America/New_York EWT
America/New_York EDT
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ramesh FL
Sent: Monday, February 21, 2005 6:04 PM
To: Christian.Antognini@xxxxxxxxxxxx
Cc: oracle-l
Subject: Re: How to get timezone in 9i
On Fri, 18 Feb 2005 16:42:34 +0100, Christian Antognini
<Christian.Antognini@xxxxxxxxxxxx> wrote:
> >I need to get international time zones in 3 letter format, something
> >like EST,CDT ....etc.
>=20
> ALTER DATABASE SET TIME_ZONE =3D3D 'EST';
>=20
> Then when you select DBTIMEZONE you get the correct format.
>=20
> Notice that you cannot alter the database if it stores tables with =3D
> TIMESTAMP WITH LOCAL TIME ZONE columns.
> --
------------------------------------------------------------------------
---
Hi,
I am using Oracle 9.2 on Win2k Pro. Here is a cut and paste of SQL
PLus:
=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
11:43:19 SQL> sho user
USER is "SYS"
11:43:22 SQL>
11:43:25 SQL> select dbtimezone from dual;
more...
DBTIME
------
-07:00
Elapsed: 00:00:00.04
11:43:32 SQL> select sessiontimezone from dual;
more...
SESSIONTIMEZONE
------------------------------------------------------------------------
---
-08:00
Elapsed: 00:00:00.03
11:43:40 SQL>
11:43:41 SQL> ALTER DATABASE SET TIME_ZONE =3D'EST';
ALTER DATABASE SET TIME_ZONE =3D'EST'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE
Elapsed: 00:00:00.01
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1) What is the difference between session time zone and db time zone
and why is there an hour diference. Also, I am in Eastern Time (which
is 5 hrs behind GMT). Why do I get -7:00 and -8:00 ?
2) ALTER DATABASE SET TIME_ZONE, does not seem to work.
Thanks.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
