Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gdeconto
Posting Yak Master
107 Posts |
 Posted - 2004-01-06 : 16:01:59
|
| came across something today that I hadnt noticed before. has anyone else seen this:declare @float1 float, @float2 float, @float3 floatset @float1=1.7550092368907204set @float2=round(@float1, 4)set @float3=round(@float2, 2)select @float1, @float2, @float3, round(round(1.7550092368907204, 4), 2), round(1.7550092368907204, 2)you will note that rounding float values generates results that are different than if you round the actual numbers.the calculations should both have rounded to 1.76 but @float2 and @float3 have rounded to 1.75.I have been storing, possibly incorrectly, dollar values in float fields that are going to be undergoing a fair bit of manipulation/calculation.should I have been doing something else, such as using decimal fields?dont decimal fields have issues of their own when it comes to rounding errors after manipulation/calculation??help!?!? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 16:10:45
|
| Float are not exact numbers (they are approximate), so you should definitely not use them for money data. Decimal or money data type is what you would want to use.From BOL:Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.Tara |
 |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2004-01-06 : 16:18:00
|
| Thx Tara.I know I can use the decimal type, but I was concerned about similar rounding issues since the monetary values will be undergoing a fair bit of manipulation (multiplied by some other values, divided by others, etc).ie: I havent used decimal fields that much and want to make sure that by converting the data types to decimal that I am not exchanging one set of problems for another... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 16:28:15
|
| Regardless if the decimal data types has problems, you should not be using float for monetary data. Float is an approximation of a value. You could wind up having less money or more money. But I am not aware of any rounding/manipulation problems with money or decimal data types.Tara |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-01-07 : 02:14:58
|
| ...as long as you specify a precision and scale that's wide enough to cover your requirements.OS |
|
|
|
|
|
|
|
|
rounding issue/question in sql2k