Thanks for the question, Randy.
Asked: November 01, 2016 - 10:59 am UTC
Last updated: November 02, 2016 - 10:32 pm UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Tom,
I am writing some code to check if the number of hours between two dates (in HH24MI) is equal to 0.5 hour, but the results vary depends on where the "24" factor is placed.
Here is my testing script:
>>>>>
SELECT
-- case 1: 1130 - 1100 = 0.5 hour (24 on LHS)
CASE
WHEN (TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) * 24 = 0.5
THEN
'EQUAL TO 0.5 HOUR'
ELSE
'NOT EQUAL TO 0.5 HOUR'
END case_1,
-- case 2: 1130 - 1100 = 0.5 hour (24 on RHS)
CASE
WHEN (TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) = 0.5 / 24
THEN
'EQUAL TO 0.5 HOUR'
ELSE
'NOT EQUAL TO 0.5 HOUR'
END case_2,
-- case 3: 1130 - 1030 = 1 hour (24 on LHS)
CASE
WHEN (TO_DATE('1130', 'HH24MI') - TO_DATE('1030', 'HH24MI')) * 24 = 1
THEN
'EQUAL TO 1 HOUR'
ELSE
'NOT EQUAL TO 1 HOUR'
END case_3,
-- case 4: 1130 - 1030 = 1 hour (24 on RHS)
CASE
WHEN (TO_DATE('1130', 'HH24MI') - TO_DATE('1030', 'HH24MI')) = 1 / 24
THEN
'EQUAL TO 1 HOUR'
ELSE
'NOT EQUAL TO 1 HOUR'
END case_4,
-- case 5: 1100 - 1000 = 1 hour (24 on LHS)
CASE
WHEN (TO_DATE('1100', 'HH24MI') - TO_DATE('1000', 'HH24MI')) * 24 = 1
THEN
'EQUAL TO 1 HOUR'
ELSE
'NOT EQUAL TO 1 HOUR'
END case_5,
-- case 6: 1100 - 1000 = 1 hour (24 on RHS)
CASE
WHEN (TO_DATE('1100', 'HH24MI') - TO_DATE('1000', 'HH24MI')) = 1 / 24
THEN
'EQUAL TO 1 HOUR'
ELSE
'NOT EQUAL TO 1 HOUR'
END case_6
FROM DUAL
<<<<<
And here is the result, that I expect CASE_1 should also return "EQUAL TO 0.5".
>>>>>
CASE_1 CASE_2 CASE_3 CASE_4 CASE_5 CASE_6
--------------------- --------------------- ------------------- ------------------- ------------------- -------------------
NOT EQUAL TO 0.5 HOUR EQUAL TO 0.5 HOUR EQUAL TO 1 HOUR EQUAL TO 1 HOUR EQUAL TO 1 HOUR EQUAL TO 1 HOUR
<<<<
My question is: Should we always put the 24 on the RHS of the equation for such comparison?
Many Thanks.
Randy
with LiveSQL Test Case:
and Connor said...
Thank for the livesql test case ! Awesome !
This is due to floating point arithmetic not being 100% precise. If we take your first example, and adjust the formatting, the reason becomes evident
SQL> col x format 999.99999999999999999999999999999999999999999
SQL> SELECT (TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) * 24 x,
2 -- case 1: 1130 - 1100 = 0.5 hour (24 on LHS)
3 CASE
4 WHEN (TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) * 24 = 0.5
5 THEN
6 'EQUAL TO 0.5 HOUR'
7 ELSE
8 'NOT EQUAL TO 0.5 HOUR'
9 END case_1
10 from dual;
X CASE_1
---------------------------------------------- ---------------------
.49999999999999999999999999999999999999920 NOT EQUAL TO 0.5 HOUR
Some rounding at a reasonable precision will fix the comparison
SQL> SELECT
2 round(
3 (TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) * 24
4 ,3) x,
5 -- case 1: 1130 - 1100 = 0.5 hour (24 on LHS)
6 CASE
7 WHEN round((TO_DATE('1130', 'HH24MI') - TO_DATE('1100', 'HH24MI')) * 24,3) = 0.5
8 THEN
9 'EQUAL TO 0.5 HOUR'
10 ELSE
11 'NOT EQUAL TO 0.5 HOUR'
12 END case_1
13 from dual;
X CASE_1
---------------------------------------------- ---------------------
.50000000000000000000000000000000000000000 EQUAL TO 0.5 HOUR
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment