This call -
SELECT ((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS')
- TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual
Produces a result of 95 - which is what you would expect from the calculation.
This call -
SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS')
- TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual
produces a result of 94.
The suggested fix is -
SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS')
- TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000+.00001) from dual
This solution seems wrong to me - can anyone suggest a better option? The actual requirement is to count the whole minutes of difference between two dates.
Excellent question.
The date arithmetic is not entirely accurate due to datatype conversions here.
TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS')
- TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'))
= .0659722222222222222222222222222222222222000000000000000
But
.0659722222222222222222222222222222222222000000000000000
* 1440
=94.9999999999999999999999999999999999999700000000000
Which means that none of TRUNC, ROUND, CEIL and FLOOR will work in all cases.
To solve this problem, you need to be able to convert each date to an integer before performing arithmetic on it, e.g.:
select FLOOR((TO_CHAR(TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS'),'J') * 1440
+ TO_CHAR(TO_DATE('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS'),'SSSSS') / 60)
- (TO_CHAR(TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'),'J') * 1440
+ TO_CHAR(TO_DATE('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS'),'SSSSS') / 60))
from dual;