Date/Time Difference in Oracle

I have one problem left with my sql when calculating a time difference.  Assume EndDate = 03/31/2009 02:00:47 and BeginDate is  03/31/2009 02:00:46.  I should get 1 second.  I am getting 00:00:00.  Everything above 1 second works just great.  This is what I'm using:

TO_CHAR(TO_DATE(TRUNC(MOD(enddate-begindate,1)*86400),'SSSSS'), 'HH24:MI:SS')
Who is Participating?
mrjoltcolaConnect With a Mentor Commented:
Its because of the order of your arithmetic. Oracle subtracts 2 date values and returns a floating point value that represents days.  enddate - begindate for 1 second different is really: .00001157

You then are converting it to seconds, but as a floating point value,  you get rounding errors, and its less than thje integer 1 (actually 0.999 something), so when passing that to TO_DATE( ..., 'SSSSS') Oracle isn't going to round UP, its just goint to give you the "SECOND" component, which is 0.

Instead, convert the values into seconds _before_ doing the arithmetic on them.

select TO_NUMBER(TO_CHAR(enddate, 'SSSSS'))
     - TO_NUMBER(TO_CHAR(begindate, 'SSSSS'))
Add see what you get.

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
question: are the date/time values you showed for enddate and begindate really exactly that, or do they eventually have some milliseconds?
marfi95Author Commented:
they are exactly that.  the fields are DATE type in the database
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you should TO_CHAR(begindate, 'HH:MI:SS.NNN') and TO_CHAR(enddate, 'HH:MI:SS.NNN'), please?
schwertnerConnect With a Mentor Commented:
Excuse me, what was your question?
SQL> SELECT TO_DATE('03/31/2009 02:00:47', 'mm/dd/yyyy hh:mi:ss') -  TO_DATE('03/31/2009 02:00:46','
mm/dd/yyyy hh:mi:ss') as dif FROM dual;
SQL> SELECT (TO_DATE('03/31/2009 02:00:47', 'mm/dd/yyyy hh:mi:ss') -  TO_DATE('03/31/2009 02:00:46',
  2  mm/dd/yyyy hh:mi:ss')) *24*68*60 as dif FROM dual;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.