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