Re: Converting Numbers to Dates

From: Charles Jardine <cj10_at_cam.ac.uk>
Date: 1997/03/11
Message-ID: <33259457.121F@cam.ac.uk>#1/1

Andy Smith wrote:
>
> --
> I have a column in a table that stores the number of seconds
> since 1970, as a number ( datatype NUMBER )
>
> How can I convert this to a date e.g 11-MAR-1997

Convert it to days by dividing by the number of seconds in a day, and then add it (as a number) to the Oracle DATE representation of 1st January 1970.

E.g.

SQL> select to_char(
  2 to_date('01-JAN-1970','DD-MON-YYYY') + ( 844275235 / (60*60*24)),   3 'DD-MON-YYYY HH24:MI:SS') mydate
  4 from dual;  

MYDATE

---

02-OCT-1996 16:53:55   SQL> Note that Un*x datestamps are always in GMT, whereas the value of SYSDATE in Oracle is in local time. Received on Tue Mar 11 1997 - 00:00:00 CST