Converting Float to decimal(5,1)
-
I could not find a good search on this topic...
We have hundreds of stored procedures which convert decimal(5,1) into a float (first through a #TempTable then than data is loaded to a physical table with float as the datatype).
We use this information for very complicated and precise percentages for reports. On occassion, the float designation adds additional decimals which through the percentage off. For example, instead of getting 100% we may end up with 100.1%. This is in fact incorrect; the end result should be 100% even.
I will not go into the code because that is not the issue. We can fix these stored procedures over time. Also, all the recordsets from the stored procedures go through a standard stored procedure which then puts the data into a physical table. The final stored procedure accepts the data as a float. We can convert that to decimal(5,1). But that data is then placed into a table field which is designated as a float. (Heaven only knows why it was designed this way, but it has to be fixed).
We tried a quick update of the table float column by ALTER TABLE ColumnName decimal(5,1) NULL. This resulted in arithmatic overflow problems. Not sure why as this table cannot contain data over 100%, but since it is a float, the number of decimal places could be very significant.
Now we have added a new column defiined as decimal(5,1) and did an update from the existing float column, assuming it would be an implicit conversion. It ran a long time and I left before it was finished.
I have also tried testing the following:
DECLARE @float float
-- SELECT @float = 2.10000000001
SELECT @float = 100.9999999999999999999
SELECT @float, CONVERT( varchar, @float), CONVERT( decimal(5,1), CONVERT( varchar, @float))
My issue is one of keeping data integrity. If the update converting the float to decimal(5,1) caused and arithmatic overflow, I obviously cannot check the column against the old column to make sure they both match. (I guess I could with a loop or cursor checking each value until I hit that error,
but I am hoping for a more simple solution). Has anyone had experience with this and can you give me a pointer?
And secondly, why would you use a float datatype? They seem so very unreliable to me...
Thanks in advance.
I wasn't born stupid - I had to study.
-
It seems very common among 3rd party app vendors to use float as an fits-all-backends number-with-decimal type datatype. (this is my own belief)
I've even seen this in financial systems, which yet again proves that 3rd party app vendors have no clue.
Floats are intended to use in scientific areas. Floats are very inappropriate when dealing with money. Float is an imprecise datatype opposed to decimal, which is a precise datatype. When using float, you will experience rounding errors - not so good (again) when dealing with monetary data.
Bottom line is, if you have money values (or any other values required to be precise) stored in floats, you basically have corrupted data. There's no way (not 100% anyway) to retrieve exactly the values what once was entered, because of how floats work.
BOL has more info on this subject as well.
/Kenneth
-
Thanks Kenneth. I did read about it on BOL. It is very imprecise and I simply do not see any application that it would serve. Even if 3rd part apps use it, they have to know it will yield unreliable data. Strange days; even if making a quick buck is the actual desire, not having good code will make for a one time sale and no good name branding for a project, (not to mention how much it will upset a good coder for having to put such a crummy solution into practice).
[This is not my application, but I can make changes over time, so we are stuck for a while with having this idiotic float as the datatype until we can get to all locations that need the change]. What also was odd is decimal and numeric are equivalent in SQL Server per BOL. I guess people got used to using one or the other, so SQL decided to keep both...
I started my career in the sciences and I still don't still see the utility of a float as the trailing decimals are not real input.... Good science requires precision and reproducibility. Weird.
Back to the question, have you or anyone else had experience in this conversion and do you have suggestions? It almost seems to me that converting to varchar then to decimal(5,1) seems to be the best approach; simply because it forces the first few characters to remain as is and not round or change in other fashion. Then, once converted to decimal(5,1) you keep your precision. One would think an implicit conversion would work, but too many arithmetic overflows took place when we tried that.
Like I said, I want to be lazy on this one, so I am hoping not to have to do individual conversion and verification, (i.e., a loop or cursor). But I do want integrity as this data will portray big money.
We still have hundreds of stored procedures which use float that must be changed over time, so I need a good conversion string that will change those values to decimal(5,1) consistently. We have a standard stored procedure in which most all of our data retrieval stored procedures must dump their output. This procedures prepares the data for our final report output, (it takes all output and adds new fields so our reports have a consistent output). In between fixing all these dataset calling stored procedures, this standard report preparation one can do our conversion on the fly. Hence, I need a reliable conversion string that will convert these floats into decimal(5,1).
Any code examples will be greatly appreciated....
I wasn't born stupid - I had to study.
-
Well, I can't answer for if it's odd or not - but decimal and numeric are synonyms, as are float and real.
I know that floats are desirable in cases that deal with scientific calculations and stuff, though I have no good example of when it's applied.
The trouble with floats is that you have rounding errors due to it's imprecise nature. afaik, the way to 'convert back' is to round with enough scale so that the rounding error ends up as far back so it's insignificant.
A float may be larger than a decimal(5,1), so you may have to check your data if that's the cause of the overflows...
/Kenneth
-
The use of float aside, I think there is a problem with your statement.
If you are converting numeric(5,1) to float then you will get either an exact value or an approximation with a lot of decimal digits. The problem you stated with 100.1 is not a problem as 100.1 in a numeric(5,1) will be converted to 100.09999999999999 in a float and then 100.1 when converted back to numeric(5,1). If you convert 100.09 to numeric(5,1) you will get 100.1 due to rounding. Any value stored in a numeric(5,1) converted to float and back again will always give you the original value, the only time rounding can take place is when the value stored is not originated from the datatype used.
As for the overflow, I agree with Kenneth, you must have a value larger than numeric(5,1).
Kenneth, as for float and real being synonyms, that is not quite true, real is a synonym of float(24) and is only 4 bytes and you cannot specify the mantissa whereas with real you can and it can be upto 8 bytes.
Far away is close at hand in the images of elsewhere.
Anon. -
Yeah, well... I know.. just didn't bother to paste all that stuff from BOL
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).
/Kenneth
-
aaaaaaarrrrggggghhh! Everything I just wrote got lost.
First, Kenneth, thanks for the extra work on my behalf. All I meant was, I did go through BOL and found float to be a booger and no information on converting it to a more precise datatype. Thanks for your extra work though...
I am making a new table and bringing in the necessary data and will work on converting what is already there and verifying integrity. (I will look for the overflow - that should be simple).
David, the problem is, the original data comes into a #TempTable as 100.0, (decimal(5,1) and the #TempTable column is defined as float. Once this converstion happens, on occassion the 100.0 is later rounded to 100.1. I can fix this with flow control forcing 100.0 when anything over that amount is encountered. Where the probelm lies is with something like 30.2. Once it becomes a float, the rounding may make that 30.3. These are percentages are used on large amounts of money, so a 0.1 percent can be very significant.
What I am hoping for is a reliable conversion script which will ensure the conversion from float to decimal(5,1) is precise and does not round incorrectly. Once I have completed transitioning the existing data, our new column will be decimal(5,1). But, we still have a lot of SPs that convert the data to float before passing it to the final table. We will change these over time, but there are hundreds of them and it will take some time. Hence, in the interim, I am hoping to use our standard Report Format SP to reliably convert this data from float into decimal(5,1). I found from some early testing that converting to varchar first prevents inaccurate rounding, then I can use charindex to find the first decimal location and prevent inaccurate rounding before converting to decimal(5,1). This seems like a long way around, but we have already seen problems with straight conversion, (which you would think would be implicit).
It seems not many people have had this problem, but if any one else can give me a better solution or approach, that would be greatly appreciatee.
Thanks again.
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply