1

I originally asked this here, but had to ask separately.

Why is it that I'm getting values like 2.01000000 from the database, even though I am storing only decimal 2.01? In my database I see it as 2.01 and not 2.010000. The field in MS Access is of type decimal, and I store it as 2.01 itself. I am pulling the value like this:

 while(reader.Read())
        Convert.ToDecimal(reader[i]);

I get the value as 2.010000000. Why? Is there a better approach to read decimal values from database? I have set the field's precision as 28 and scale factor as 18..

Community
  • 1
  • 1
nawfal
  • 70,104
  • 56
  • 326
  • 368

2 Answers2

2

Related to the fact that System.Decimal is internally sensitive to degrees of precision, SQL's decimal is even more so - with it specified as part of the type.

Scale of 18 and Precision of 28 means there'll be 18 significant digits after the decimal point in the database. (28 digits precision total, 18 after the point, leaves 10 before).

Which access happens to include in the representation it sends to the DAO code.

And decimal happens to note in the parsing done.

And hence you can see this.

Personally, I think this is a flaw; either decimal should express precision as an explicit property, or it should hide it. Maybe there's some justification I can't think of, but none come to mind.

Jon Hanna
  • 110,372
  • 10
  • 146
  • 251
0

A decimal stores those extra zeros by design. You could maybe set the level of precision you want by rounding the number to the correct precision:

myNumber = decimal.Round(myNumber, 2);

This will change the decimal precision to a precision of 2 (and apply rounding).

Adrian Thompson Phillips
  • 6,893
  • 6
  • 38
  • 69
  • But how do I know what precision should I set? I sometimes have a value 2.01 and sometimes 2.0000001. How to deal with this – nawfal Jan 17 '12 at 17:03
  • Any comparisons of decimals will still work, 2.010000 will equal 2.01. Whether it holds any amounts of zeros isn't an issue. The decimal values will even take up the same amount of storage in memory. If you're converting the value to a string then just do a Trim('0'). – Adrian Thompson Phillips Jan 18 '12 at 08:41