Return
-
Title
Different results in Toad vs SQLPLUS when selecting datatype TIMESTAMP WITH LOCAL TIMEZONE -
Description
Setting the session time_zone does not function correctly in TOAD 10.1.0.7. There are different results when selecting datatype TIMESTAMP WITH LOCAL TIMEZONE via TOAD vs sqlplus.
STEPS TO REPRODUCE:
FROM TOAD:
SQL> CREATE TABLE TEST_TZ
(
TEST_ID NUMBER(4) DEFAULT 0,
TRANS_DATE TIMESTAMP(2) WITH LOCAL TIME ZONE,
CREATE_DATE TIMESTAMP(2) WITH LOCAL TIME ZONE
);
select dbtimezone, sessiontimezone from dual;
+00:00 -06:00
Insert into TEST_TZ VALUES
(101,TO_TIMESTAMP_TZ(10/31/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM),
TO_TIMESTAMP_TZ (11/02/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM));
select * from test_tz;
101 10/31/2009 9:59:33:93 AM 11/2/2009 9:59:33.93 AM
alter session set time_zone = America/Chicago;
select dbtimezone, sessiontimezone from dual;
+00:00 America/Chicago
Insert into TSAPP_LOCAL.TEST_TZ VALUES
(102,TO_TIMESTAMP_TZ(10/31/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM),
TO_TIMESTAMP_TZ (11/02/2009 9:59:33.93 AM,fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM));
select * from test_tz;
101 10/31/2009 9:59:33:93 AM 11/2/2009 9:59:33.93 AM
102 10/31/2009 8:59:33:93 AM 11/2/2009 9:59:33.93 AM
Now perform selects from SQLPLUS:
SQL> select dbtimezone, sessiontimezone from dual;
+00:00 -06:00
SQL> select * from tsapp_local.test_tz1;
101 31-OCT-09 09.59.33.93 AM 02-NOV-09 09.59.33.93 AM
102 31-OCT-09 08.59.33.93 AM 02-NOV-09 09.59.33.93 AM
SQL> select dbtimezone, sessiontimezone from dual;
+00:00 America/Chicago
SQL> select * from tsapp_local.test_tz1;
101 31-OCT-09 10.59.33.93 AM 02-NOV-09 09.59.33.93 AM
102 31-OCT-09 09.59.33.93 AM 02-NOV-09 09.59.33.93 AM
Note the differences in the select output....
Sign In Required
You need to be signed in and under a current maintenance contract to view premium knowledge articles.