odd dates being retrieved

From: Mike Hasenkamp <michael_hasenkamp_at_3com.com>
Date: Fri, 26 Feb 1999 12:34:31 -0800
Message-ID: <7b70gf$6rg@masters0.InterNex.Net>

Hello,

I'm trying to fine tune a sql statement which will return a date.

The date values in my database are stored in seconds with a base (start date) of January 1, 1970.

Whenever we try the following sql, we get close to the date we are expecting, but we seem to be off by a few hours.

Here's the sql we're trying.

select to_char( (to_date('January, 01, 1970,00:00:00', 'Month, DD, YYYY, HH24:MI:SS') + ((912821111)/(60*60*24)) ), 'Month, DD, YYYY, HH24:MI:SS') "Date" from dual

The value returned is:

Date

---

December , 05, 1998, 01:25:11 (this is AM, I think) 1 row selected.

I expect the value to be December, 04 1998, 5:25:11 (this should be PM)

Other calculations give us dates that are close, but not necessarily off by the same number of hours. So, this seems to be a random problem. I'm sure it isn't a random problem, but more of a "new person to sql" problem.

We are close to the answer, but close is not good enough. Is there a tweak that we need to do to the sql to get this to come out "right" each time?

Your assistance is much appreciated.

Mike Received on Fri Feb 26 1999 - 14:34:31 CST