[EMAIL PROTECTED] wrote:
T&B <[EMAIL PROTECTED]> wrote:
select cast( 141.70 * 100 as integer)
gives the incorrect 14169
14169 is the correct answer, believe it or not. There is
no such thing as the number 141.70 in a 64-bit IEEE float.
The closest you can get is
141.69999999999998863131622783839702606201171875
which is what SQLite uses internally. If you multiple
that value by 100 and take the integer part, you are
left with 14169.
but this workaround:
select cast( round(141.70 * 100, 0) as integer)
gives the correct: 14170
Now the question is, does this fix the problem, or only for some
numbers?
This should fix your problem. The round() function takes
the nearest integer rather than simply truncating the fractional
part.
So will the simpler alternative I suggested earlier without the all the
overhead of the round function.
select cast(141.70 * 100 + 0.5 as integer)
By adding 0.5 (which can be expressed exactly as a floating point
number) to the value you change the value so that any original value
with a fractional part greater than or equal to 0.5 will have its
integer part increased to the correct value before the conversion to
integer truncates the unwanted fractional part.
141.70 is actually 141.6999... which is multiplied by 100 to give
14169.999..., after adding 0.5 you have 14170.4999...
which is truncated to 14170 by the cast.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
