1

When inserting a float into a SQL Server database, I'm getting: 

5.03000020980835

Which is not the exact value which is being gathered.

How I'm gathering this float? Through a text box control to be converted to float

How I'm working with the data currently: 

   private void PayRateTextBox_TextChanged(object sender, EventArgs e)
   {
       PayRateBox = float.Parse(PayRateTextBox.Text);
   }

The above is setting an internal float to the current updated textbox which is set: 

 private float PayRateBox = 0;

Then inserted as: 

string Query = "INSERT INTO shifts (ShiftDate,WeekNumber,Hours,PayType,Rate) " + 
               "VALUES(@Date, @WeekNo, @Hours, @PayType, @Rate)";

and bound to the query via bound parameters: 

CMD.Parameters.Add("@Rate", SqlDbType.Float);
CMD.Parameters["@Rate"].Value = PayRate;

The default text in the TextBox is set to 5.03, so somewhere along the lines other data is being appended to make the overall value increment. I have tried to trace where this is happening, but cannot find out how and why this is happening. Perhaps I'm overlooking something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daryl Gill
  • 5,464
  • 9
  • 36
  • 69
  • 7
    Floating point numbers are not exact, so this should not be surprising. – John Saunders Aug 28 '14 at 00:20
  • 1
    @GrantWinney After making changes to the Table and the active code. It works like a charm. New structure for the table is `decimal(3,2);` as apposed to `float` – Daryl Gill Aug 28 '14 at 00:28

1 Answers1

4

Precision of float is limited to 7 significant digits. In your case it means 5.030000. Values at the rest of decimal places are undefined.

To improve precision use either double with precision of 15-16 significant digits or decimal with 28-29 significant digits.

Michal Hosala
  • 5,570
  • 1
  • 22
  • 49
  • Can accept as answer in 1 minute, though find it a little design fault if precision is lost after 7 digits – Daryl Gill Aug 28 '14 at 00:32
  • Well sometimes a precision of 7 digits is enough, depending on problem you are trying to solve. Also, `float` only has a size of 4B while `double` is 8B and `decimal` 16B. So floats will save your memory. – Michal Hosala Aug 28 '14 at 00:34
  • @DarylGill So how would you design a 32-bit floating point number that has more than 7 digits of precision? Best you can do is 10 digits if you leave no room for an exponent (essentially an `int`) – D Stanley Aug 28 '14 at 00:35
  • @DarylGill It's the nature of the beast. You trade the ability to represent a very wide range of values for an inability to accurately express any value. When I was in college I found an entire department where none of the **teachers** understood this--nobody would believe a loop with a termination condition of x = x + 1 would ever exit. – Loren Pechtel Aug 28 '14 at 00:36
  • Arbitrary precision decimal-point arithmetic is possible (based on something called p-adic arithmetic), but that's not the way modern machines implement non-integrals. Yes, it really is something lacking. – jpaugh Aug 28 '14 at 00:53