Problem with DBTIMEZONE

  • From: "Sandra Becker" <sbecker6925@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 16 Oct 2008 14:02:13 -0600

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

Other related posts: