Oracle Database - Query to convert date difference in hours, minutes and seconds format

Asked By sunny C on 20-Feb-13 07:00 AM
Hi All,

I am stuck at converting difference between two datetimes into hours, minutes and seconds format. I created the below sql but is not working fine. Can you please help me in solving this issue.
The below sql is working fine.

select (((TO_DATE('04/02/2013  16:48:57','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013  21:04:07','DD/MM/YYYY HH24:MI:SS'))*24*60) - mod(((TO_DATE('04/02/2013  16:48:57','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013  21:04:07','DD/MM/YYYY HH24:MI:SS'))*24*60),60))/60 
      ||':'|| TRUNC(mod(((TO_DATE('04/02/2013  16:48:57','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013  21:04:07','DD/MM/YYYY HH24:MI:SS'))*24*60),60))
      ||':'|| TRUNC(mod(((TO_DATE('04/02/2013  16:48:57','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013  21:04:07','DD/MM/YYYY HH24:MI:SS'))*24*60)*60,60))
from dual

giving 
67:44:50

But when I use that in the live sql it is just giving 00:00:00.

Select starttime, endtime, (((TO_DATE(endtime,'DD/MM/YYYY HH24:MI:SS') - TO_DATE(starttime,'DD/MM/YYYY HH24:MI:SS'))*24*60) - mod(((TO_DATE(endtime,'DD/MM/YYYY HH24:MI:SS') - TO_DATE(starttime,'DD/MM/YYYY HH24:MI:SS'))*24*60),60))/60 
      ||':'|| TRUNC(mod(((TO_DATE(endtime,'DD/MM/YYYY HH24:MI:SS') - TO_DATE(starttime,'DD/MM/YYYY HH24:MI:SS'))*24*60),60))
      ||':'|| TRUNC(mod(((TO_DATE(ps.endtime,'DD/MM/YYYY HH24:MI:SS') - TO_DATE(ps.starttime,'DD/MM/YYYY HH24:MI:SS'))*24*60)*60,60)) as Duration,


result should be:
 
starttime endtime duration
13/02/2013  15:31:56 13/02/2013  15:32:01 00:00:05

Thank you so much for your help.


Regards,
Sunny