C# to SQL float values conversion

Question

Hello, I'm working with C# and SQL 2005. I've stored a float variable of the value 0.2 into table column of real type. When I opened the table to check the values, I found a value of 0.200000029... It is known problem, real is approximate type. However, I've changed the value using management studio to 0.2 and reloaded the table. The database is now showing value of 0.2.

The problem lies somewhere between C# and SQL. Is it possible to store values from C# as from SQL management studio? I suppose it is, but don't know how.

Can anyone help? Thanks a lot.

Tomas

Answer 1

If you store .2 and want to get back ONLY .2, use the decimal data type. float values in SQL Server are real IEEE float values, which means you can wind up with NaN values in the fields. Doing ANYTHING with those values will cause your statement to crash. Unless you KNOW you really need a float value, you're far better off using decimal.

To directly answer your question, the .200000....029 you're seeing is just the nature of floating point values.

Answer 2

As pointed out, what you are seeing is a side effect of how floating point numbers are stored in binary.

For 0.2f, the exact representation is 0.20000000298023223876953125

See Jon Skeet's article for more information and code for how that was derived:

http://www.yoda.arachsys.com/csharp/floatingpoint.html