October 20, 2004 at 2:03 pm
I am in the process of designing a DTS package to import more customer data from another database. In the source database I have a field with a data type of money. The corresponding field in the target database is float. When I bring the data into the target database without converting it I am getting odd results. The source record reads, for example 46.4000 and after importing the target field now holds the value of 46.399999999999999. Another source record reads 74.2500 and after importing the target field holds the value of 74.25. What is the difference and how can I get all of the target records to hold the exact value of the source table with 2 decimal places? I have tried cast and converting to different data types within a select statement to try to verify the data before I import it and the results seem to be the same. Is the only way to verify this going to be for me to run the import each different way until I get the result that I want?
Please offer suggestions as this is a very urgent issue.
Thanks much...
October 20, 2004 at 4:12 pm
I would first reconsider storing your data as a float if you care for very precise number storage.
However, if you have no choice you can try using this hack to force the storage as desired.
select
float_column=str(MONEY_COLUMN,10,4)+'000001'
from TABLE
You will need to manage the string length and precision, and the '000001' on the end to is an attempt to trick the float estimation round. This solution seems to give a better number than cast/convert to real and allows a larger number to be stored than decimal (which in this example doesn't work from a straight cast/convert anyway).
Good luck.
October 20, 2004 at 6:33 pm
I agree with Osoba - have played with Round, casting to decimal etc and nothing works elegantly. If you do not change the datatype of the column in the target database, you will continue to have problems. Every time you query the target database, you will have to CAST the float column before you can rely on things adding up properly.
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2004 at 1:39 pm
Part of your question was "What is the difference?"
An exact numeric type (money, smallmoney, decimal, numeric) stores an exact representation of a decimal number by storing each digit separately.
An approximate numeric type (float, real) stores the number as a binary fraction. It is more compact than exact numeric and faster for calculations, but not good if you have to worry about roundoff errors in the 15th decimal place.
You can store the exact value of 0.25 (1/4) in binary, but not the exact value of 0.40 (2/5).
74.2500 = 1001010.01 exactly
46.4000 = 101110.01100110011001100110011... somewhere around the 56th bit you have to stop and lose some accuracy.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply