Re: converting milliseconds to Oracle DATE

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 May 2006 16:26:52 -0700
Message-ID: <1148081212.115554.202020@i40g2000cwc.googlegroups.com>

Dave, if your number is really milliseconds then unless you can round to the nearest second you have a minor problem. An Oracle date datatype only keeps time to the nearest second.

The timestamp datatype can record fractional seconds.

The difference between any two dates in Oracle is a number representing days and fractions thereof. So convert your number to days and fractions thereof and add this to a to_date on your reference date.

Convert from number of seconds since reference date to Oracle date
--
select to_char(to_date('01-JAN-1970','DD-MON-YYYY') + ( &no_sec_since_70 / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS') "New Date" from sys.dual ;

HTH -- Mark D Powell -- Received on Fri May 19 2006 - 18:26:52 CDT