I thought DATE type's precision was only to seconds. To use milliseconds
or more precision you need a TIMESTAMP field type.

-----Original Message-----
From: Louis.Fenichel
[mailto:oracledba-ezmlmshield-x79806142.[Email address protected]
Sent: Wednesday, January 03, 2007 12:49 PM
To: LazyDBA Discussion
Subject: Adding seconds to a date - still stuck

All,
Any ideas why I'm losing the seconds and milliseconds when I add dates?
Is this a problem with integer arithmetic? I think I'm attempting to
add 4988.00111 days, the answer should be 2006-08-29 00:01:35.48928.
tks

SQL> select MO_P1_TIME from modis_pass1 where rownum=1;

MO_P1_TIME
--------------------------------------------------
430963295.489281

SQL> select to_char(cast(to_char(to_date('01-JAN-1993','DD-MON-YYYY') +
( MO_P1_TIME/(24*60*60))) as TIMESTAMP),'YYYY-MM-DD HH24:MI:SS.FF5')
from modis_pass1 where rownum=1;

TO_CHAR(CAST(TO_CHAR(TO_DATE(
-----------------------------
2006-08-29 00:00:00.00000

SQL> select MO_P1_TIME/(24*60*60) from modis_pass1 where rownum=1;

MO_P1_TIME/(24*60*60)
---------------------
4988.00111



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html






Oracle LazyDBA home page