0

I have a table in SQL Server that has a column with datatype float. I wrote a stored procedure for inserting the record and it also accepts the parameter as float.

However Entity Framework generated the method with datatype double. So when I add 0.3 from my .NET client, it is changed to 0.300000011920929 however I need to add the exact value I added that is 0.3.

Any ideas how to fix this?

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Muhammad Usama Alam
  • 21
  • 7
  • 1
    Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) It's just how binary floating points work – Charlieface Jul 11 '21 at 11:01
  • Suggest you read this [floating point guide](https://floating-point-gui.de/) if you want to understand why this happens. And you should read it. – SMor Jul 11 '21 at 12:18
  • Or https://stackoverflow.com/q/2100490/861716 – Gert Arnold Jul 11 '21 at 16:51

1 Answers1

1

In SQL, as in other languages, the float data type cannot be used to store exact base10 decimal values because a float does not always have an exact representation (https://blog.greglow.com/2018/01/15/sql-newbie-mistake-1-using-float-instead-decimal/). Try using the decimal data type instead, which in SQL is designed to store decimal values precisely.

See also Is floating point math broken?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Pete
  • 103
  • 8
  • Of course they can be used to store exact values, they just have to be base2 fractions (eg 0.5 or 0.25 decimal), which 0.3 (decimal) is not – Charlieface Jul 11 '21 at 11:08
  • @Charlieface, good point. I've edited the answer accordingly – Pete Jul 11 '21 at 16:14
  • When I am inserting a record with an insert statement, it inserts the exact value but in .NET it is mapped with double data type and hence the approximate value is passed from the .NET client. – Muhammad Usama Alam Jul 12 '21 at 07:57
  • Hi @Charlieface, I couldn't understand the point, can you please elaborate more? At this stage it is hard to change the data type from float to decimal, is there any other way to keep the float datatype and store the exact value? – Muhammad Usama Alam Jul 12 '21 at 07:59
  • @MuhammadUsamaAlam Please read up on how floating point math works. You are not inserting the exact value because the value you have does not have an exact representation in floating point, neither in .NET or in SQL. If you want to store an exact decimal the use the `decimal` type – Charlieface Jul 12 '21 at 10:13