I ran into something interesting with using trunc() and different data
types:
The following is a simplified from the statement we're using and
produces the same results:
=20
select trunc( ((cast(2183.68 as numeric) - cast(1 as numeric)) )*100)
/100=20
yields 2184.68
=20
select trunc(((cast(2183.68 as numeric) - cast(1 as double precision)
))*100) /100
yields 2184.67
=20
select trunc(cast(2184.68 as double precision) *100)=20
yields 218467 instead of 218468
=20
This only happens on certain ranges of numbers. Doing the same thing
with the number 3183.68 yields the same result in both cases. It only
appears to happen when a number is declared as a double and there is no
number past the last significant digit or the number past the last
significant digit is a zero AND falls within a certain range of numbers.
For instance select trunc(cast(2184.681 as double precision) *100)
yields 218468, but select trunc(cast(2184.680 as double precision) *100)
yields 218467.
=20
I already made sure everything we're using is just defined as numeric to
avoid the issue.