0

I am having a hard time getting around this issue.

I am working with a enterprise DB table that has a column called balance which is a float.

I am trying to insert a decimal value into it. Now in c# I tried round the decimal to 2 places then inserting it into the column and it just does this. 56.4000015258789 when my value is 56.40.

I am debugging just before my insert and it is rounded. I can't change the column . . .so how do I get around this?

I know it has something to do with how sql considers fractions.

I tried formatting the decimal to a string then parsing it to a float and that didn't work.

Thao Nguyen
  • 901
  • 7
  • 22
  • 42
  • 1
    `float` in C# only has 7 digits of precision, and can't represent all non-integer values exactly. Try `double` instead. If you need to keep the decimal representation in SQL use a `decimal` column instead – D Stanley Apr 15 '16 at 18:13
  • @DStanley I can't change the column, it really frustrating. – Thao Nguyen Apr 15 '16 at 18:15
  • @AdamV The problem is that the float value before insertion is rounded off. But when It get inside sql for some reason starts making up training numbers. – Thao Nguyen Apr 15 '16 at 18:17
  • 1
    If you can't change the column, then you are stuck with this behavior. You will just have to round at query time if you only want to see 2 digits. – Tab Alleman Apr 15 '16 at 18:17
  • @TabAlleman The problem is the enterprise software running this will blow up if I don't round it. . . . . I have seen in the dev database version of this with rounded off floats. Granted they did this in access server 2003 with VB. – Thao Nguyen Apr 15 '16 at 18:19
  • 1
    You can't change the software and you can't change the database? Then what exactly are you working on? – Tab Alleman Apr 15 '16 at 18:21
  • You'd probably see the same thing if you cast your "rounded" `float` to a `double`. The problem is that the _actual_ float value is very slightly different, but it doesn't show up that way in C# - when it gets sent to SQL the difference becomes more apparent. Have you tried using `double `in C# instead? – D Stanley Apr 15 '16 at 18:21
  • 2
    http://stackoverflow.com/a/22768244/1507566 - an explanation of why this occurs and why you can't do anything about it. – Tab Alleman Apr 15 '16 at 18:24
  • @TabAlleman I was working on converting a old Microsoft access server to a c# application. I decided to just get rid of the float in my class and made it a decimal. Why I had not done this before hand was to keep the entity in line with the table. – Thao Nguyen Apr 15 '16 at 18:26

1 Answers1

1

Pretty sure the reason for this is that SQL is approximating the value of the floats and doubles. There is no way around this it is a fundamental characteristic of how this data is stored in the database. If it was me in this position I would reassess why I cant change the column.

Your options are thus:

  1. Round the data prior to using it in your SELECT statement.
  2. Create a separate table joined by the PK and store the precise value in there.

I don't like either option to be honest.

Steve Moore
  • 168
  • 6