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 >