Re: odd dates being retrieved

In article <7b70gf$6rg_at_masters0.InterNex.Net>, Mike Hasenkamp <michael_hasenkamp_at_3com.com> wrote:
>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)

I don't know what brand of calculator you are using, but I think you should switch. I got exactly the same date AND time as Oracle.

912821111/86400 = 10565 days with a remainder of .05916 When I figured in leap years of 72, 74...96, I got a date of Dec. 5, 1998.

As for the .05916 * 86400, it works out to 5111 seconds, or 1 hour, 25 minutes, and 11 seconds.

Steve Cosner Received on Wed Mar 03 1999 - 00:12:16 CST