Substract two dates

[sfb]

Hi,

I need to substract two dates so i get the difference between them in seconds. When i do something like :

select to_date('14-02-2002 11:30:03', 'DD-MM-YYYY HH:MI:SS') - to_date('14-02-2002 10:30:00', 'DD-MM-YYYY HH:MI:SS') from dual

I get 0,0417013888888889 as a result. Not really 3 seconds. I also cant figure out this number, because 0,04... is not equal to 3 seconds.

Then i found out the ROUND statement, so i did

select round( to_date('14-02-2002 11:30:03', 'DD-MM-YYYY HH:MI:SS') - to_date('14-02-2002 10:30:00', 'DD-MM-YYYY HH:MI:SS'), "SS" ) from dual

Nice try, but no succes. ( invalid number )

Does anyone know what i do wrong?

 

[rcurva]

You may need to multiply your result with (24 hr/day * 3600 secs/hr = 86). So, for your example;

select (to_date('14-02-2002 11:30:03', 'DD-MM-YYYY HH24:MI:SS') -
to_date('14-02-2002 10:30:00', 'DD-MM-YYYY HH24:MI:SS') ) * 86400 "In Seconds"
from dual;

In Seconds
---------------
3603



Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[Turkbear]

Hi,
assuming you made a typo in your example ( since it would be 1 hour and 3 seconds not 3 seconds), and using robbie's
correct reply you get:

  1  select (to_date('14-02-2002 10:30:03', 'DD-MM-YYYY HH24:MI:SS')
  2  - to_date('14-02-2002 10:30:00', 'DD-MM-YYYY HH24:MI:SS') ) * 86400 "In Seconds"
  3* from dual
SQL> /

In Seconds
----------
         3

 

[sfb]

Thanks a lot, thats just what i was looking for.