Problem with DBTIMEZONE
Date: Thu, 16 Oct 2008 14:02:13 -0600
Message-ID: <3c5f7820810161302g675969fdub680096c5f4f71f8@mail.gmail.com>
IBM Series z9
SuSE10
Oracle 10.2.0.3 (highest release for the IBM)
Many years ago before I started working here, a developer designed a table with a column DATELOGGED NUMBER(20). They store the date in this column, after converting it using a function.
When I created the new database on the IBM prior to conversion, I took the default value for time_zone (+00:00). Now four months later, they tell me it's causing problems with this DATELOGGED column having the wrong value. It's showing a time 6 hours ahead of the time here in Denver. He asked me to alter the database and set the DBTIMEZONE to '+07:00', which I did on a test database. Now he's telling me that won't account for daylight saving time and I need to fix it. (Yes, I specifically asked him did he want to take daylight saving time into account and he replied no. I have the email trail to prove it.)
Now we're getting the following error with the following code:
select TO_DATE('01-JAN-1970','DD-MON-YYYY') + 1221152048851/(24*60*60*1000) +
TO_NUMBER(TRANSLATE(TO_CHAR(DBTIMEZONE), ':',
'.'))/24 from dual
/
13:54:22 ==> select TO_DATE('01-JAN-1970','DD-MON-YYYY') + 1221152048851/(24*60*60*1000) +
TO_NUMBER(TRANSLATE(TO_CHAR(DBTIMEZONE), ':',
'.'))/24 from dual
/
13:59:57 2 13:59:57 3
TO_NUMBER(TRANSLATE(TO_CHAR(DBTIMEZONE), ':', '.'))/24 from dual
*
ERROR at line 2:
ORA-01722: invalid number
I have over 50 million rows in this table. I have already suggested that they save the date as a datatype DATE, but "we don't have time to change everything". Going bald here trying to figure out what I can do. Not a good look for me.
Any suggestions would be greatly appreciated.
Sandy
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 16 2008 - 15:02:13 CDT