CODEWITHSUNDEEP
oracle-database
Asanka Amarasinghe
Asanka Amarasinghe

Reputation: 13

Decimal issue (Oracle 11G)

I was debugging an issue on one of our Oracle databases with Pl/Sql developer and found this issue.

This is the select statement I ran on Pl/SQL:

SELECT  24*(t.plan_f_date - t.plan_s_date),
t.plan_hrs,
24*(t.plan_f_date - t.plan_s_date) - t.plan_hrs
FROM active_Separate t
WHERE t.wo_no = 613484;

This is the result I got:

-----------------------
a        b         c
-----------------------
.5       .5        8E-40

Just by looking at it, a = .5 and b = .5; therefore a-b (which is c) should be 0, but it's 8E-40.

Has anyone seen this issue before?

Plan_hrs is of type NUMBER and both plan_f_date and plan_s_date are of type DATE.

Thanks in advance.

Upvotes: 1

Views: 76

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

I'm assuming you have aliased your query as follows:

SELECT  24*(t.plan_f_date - t.plan_s_date) as a,
        t.plan_hrs as b,
        24*(t.plan_f_date - t.plan_s_date) - t.plan_hrs as c
FROM ...

I'm able to produce similar results with the following test data:

WITH testdata AS (
  select to_date('00:30','HH24:MI') as plan_f_date
        ,to_date('00:00','HH24:MI') as plan_s_date 
        ,0.5 AS plan_hrs
  from dual
)
SELECT  24*(t.plan_f_date - t.plan_s_date) as a,
        t.plan_hrs as b,
        24*(t.plan_f_date - t.plan_s_date) - t.plan_hrs as c
FROM testdata t;

a: 0.4999999999999999999999999999999999999992
b: 0.5
c: -0.0000000000000000000000000000000000000008

These inexact results are because dates are accurate down to the second, subtraction of dates returns a number where 1 = 1 day, the smallest unit (1 second) is equal to approximately 0.00001157407407407407407407407407407407407407 of a day, so you're going to get small errors creeping in as soon as you do multiplication of the results.

Therefore you probably need to round the result to get 0 (zero).

Upvotes: 5

Related Questions