Float or decimal

Question

Float or decimal

Branko G

SSC Journeyman

Points: 76
More actions
December 23, 2005 at 7:14 am

#92880

Hi,
very interesting thing if i have field float number 99 is in database present 98.999999999999999 in some cases in query i become 99 in some 98.99. If i have many rows sum heve diference from +/- 0.01. How make control on these numbers, and what type of field you practicly use in ERP databases ( is any law or perscription ).
Best regards.
Branko G

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 1

I use neither float or decimal. I'm able to stick with smallmoney or money for my configuration when I need decimal precision and tiny/small/ int when I don't.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

"Books Online" describes both FLOAT and REAL as follows:

"Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented."

Why anyone would use them is totally beyond me. Unlike Jeff Foyal, I do use Decimal with the caveat that you must know your limits... for example, if you multiply two decimal numbers, one having 2 decimal places and the other having 3, you will need 5 decimal places to correctly represent the answer. The MONEY data type works fine so long as you don't need any more than 4 places of precision. Obviously the answer from my example would violate that prescision.

Typically, if I need "calculator like" precision, I'll use DECIMAL(38,20) (18 places for whole numbers, 20 places for decimal point numbers) and round to whatever precision I need either by equating to another Decimal or sometimes Money data type. In a real control pinch, I may actually use ROUND to guarantee the direction of rounding.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)