Date and time calculations
564247Feb 27 2007 — edited Mar 1 2007In the following SQL I have problems getting correct result when calculating a difference between two dates that should be 1 minute. The SQL is:
SELECT TO_CHAR (FLOOR(( TO_DATE ('26.02.2007 14:06:00',
'DD.MM.YYYY HH24:MI:SS'
)
- TO_DATE ('26.02.2007 14:05:00',
'DD.MM.YYYY HH24:MI:SS'
)
)
* 1440)
) delay
FROM ....
This gives the result 0 (because FLOOR rounds down 0.9999...), it should be 1.
We want to cut secunds so that the time 14:06:00 - 14:05:59 should get the result 1. So therefore we use FLOOR. The problem is when calculating 1 minute exactly, the result is 0.99999..., which becomes 0 with the use of FLOOR.
Can anyone help me with this problem?