Re: Problem with DBTIMEZONE

Would it make sense to change the time zone to 'US/Mountain' ? this should
take DST into consideration.
What was it set to on the old database? Was the old database a 9i database?

Bradd Piontek
  "Next to doing a good job yourself,
        the greatest joy is in having someone
        else do a first-class job under your
        direction."
 -- William Feather


On Thu, Oct 16, 2008 at 3:02 PM, Sandra Becker <sbecker6925@xxxxxxxxx>wrote:

> 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: