December 20, 2006 at 1:31 pm
Hi,
I have run into this issue and iam not sure whats the problem. The column is float datatype and sql 2000 table properties show its size as 8 and I dont see any precision. One of the records stored in this float column is 33.87. When I display this record by doign select query in SQLServer 2000 Query Analyser, the record is shown as 33.86999999. When I try the same in SQL Server 2005 management studio, the record shows the correct format i.e 33.87. I dont know what is going on here...
Here is what I did to get 33.87 in Sql query analyser. I did cast(colname as varchar) and the result is shown correctly i.e 33.87.
This issue is happening with all the records which have column datatype as float. I have more than one column as float and this issue is killing me. I can get away with the cast function but I need to know why would sql 2000 do it like this.
Thanks in advance.
December 20, 2006 at 1:43 pm
The article from BOL:
--------------------
float and real
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.
Syntax
float [ ( n ) ]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
--------------------------
Does it answer your question?
_____________
Code for TallyGenerator
December 20, 2006 at 2:16 pm
NO. Are you saying the record is saved as 33.87 but when its displayed, sql server shows approximate number data i.e 33.8699999???
I am so lost... plz clarify anyone..........
December 20, 2006 at 3:43 pm
No, the value in record is saved as FLOAT.
Closest float value to 33.87 is 33.8699999. Actually this is not right, there must be 5 more "9"s in the stored value: 33.869999999999.
What you see when you select it depends on what kind of conversion the tool you use to display the record uses to display float values.
That's why the same float value is displayed differently in SQLServer 2000 Query Analyser, SQLServer 2000 Enterprise Manager and SQL Server 2005 management studio.
You don't see the actual value in the record, you see results of different conversions to varchar.
_____________
Code for TallyGenerator
December 20, 2006 at 10:31 pm
Note that the fraction 1/3 can't be represented exactly using base 10 (decimal) notation (0.333333 is close).
Your number (33.87) can't be represent exactly using base 2 notation.
December 21, 2006 at 1:53 am
FLOAT is a very special datatype and so far I never needed to use it. I always use MONEY or NUMERIC datatype, depending on requirements.
It would be nice to know where FLOAT datatype is recommended or required. The only possibility I can think of is if you know that values could exceed the limits for all other datatypes ... but is there any other situation where you would consider using FLOAT as datatype of a column?
December 21, 2006 at 4:10 am
In scientific applications you care not about number of digits after decimal point but about precision, about trusted digits in a value.
Did you ever had problems with adjustment of money value evenly distributed over 3 items?
$1/3.0000 = $0.3333
0.3333+0.3333+0.3333 = 0.9999
Simple arithmetic operation and your precision is not 4 digits anymore, just 3 left.
For accounting it's enough to have 4 numbers because in a result of reasonable number of simple calculations you'll still have 2 trusted digits required by law.
But in science you must measure and store values from angstrems to millions of km, from nanograms to mass of the Earth, from nanoampers to kiloampers. And you must perform number of really complex calculations, and your result must have guaranteed number of trusted digits no matter how far from decimal point they are.
If they would use decimal format for the value of charge of electron computers would probably never been created.
_____________
Code for TallyGenerator
December 21, 2006 at 11:42 am
ok... what would you suggest. Shall I change the datatype for the column from float to decimal(8,2) or shall I alter the float to have something like this float(8,2)???
What do you think is a good option here. I have about 5/6 columns like this in the main table and then I have a holding table which also has same no. of columns. I have a DTS wherein I read a text file, bulk insert into holding table and from there I insert the records into main table.
Suggestions?
Thanks in advance.
December 21, 2006 at 12:26 pm
December 21, 2006 at 12:48 pm
What is the application? What does the column represent?
Steve
December 21, 2006 at 4:21 pm
Rule of thumb:
Never EVER use floating point data types to store data where two or more values are supposed to add to equal some other value. Sometimes they will, more often they will not. Neither should you use floating point data types for any intermediate operations. As the other posters have pointed out the internal representations (persietent or transient) are approximate only.
For nearly all business perposes - except perhaps the occasional complex interest rate calculation where you're going to round or truncate anyway - use a fixed precision datatype such as decimal, money or one of the integer types.
(In a former life I worked on an accounting project where some of the code was initially implemented using floating point. It never reconciled. Go figure.)
December 21, 2006 at 5:08 pm
The column represents some amount like deduction amount (insurance appl). The values we expect in that column should be in 9999999.99 format. I am inclined towards decimal(10,2). Anyone has any other suggestions or shall i stick with decimal??
December 21, 2006 at 9:05 pm
What's wrong with money datatype?
_____________
Code for TallyGenerator
December 21, 2006 at 9:54 pm
Serqiy is correct... the MONEY datatype is the way to go here... the MONEY datatype takes the same as a datetime... 8 bytes and gives you 4 decimal places to work worth. DECIMAL(10,2) takes 9 bytes.
For that matter, the SMALLMONEY datatype may actually do... it goes up to 214K, has 4 decimal places, and only takes 4 bytes.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 22, 2006 at 6:33 am
I agree Money is the way to go. Float is just trouble should be used only for Scientific data and even then maybe. It seems Microsoft put Money datatype in SQL Server for this application!
Good Holiday
Steve
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply