2

I am inserting float numbers in Microsoft SQL and some of them appears as:

7E-05
5E-05
6E-05

The other float are inserted as expected. For example this float 0.00007 is inserted as 7E-05 but this one 0.01500 as 0.015.

I was suspecting the size of float but after reducing it to x.yyyyy I still get this kind of float. Anyone know what causing this issue?

jarlh
  • 42,561
  • 8
  • 45
  • 63
BlackM
  • 3,927
  • 8
  • 39
  • 69
  • 2
    Where do they appear that way? Floats in SQL (and C#) are just numbers, so the actual _value_ appears to be stored correctly. (I'm assuming that you understand that `7E-05` and `0.00007` are the same number) – D Stanley Aug 25 '15 at 13:27
  • in the SQL. When I SELECT the value, I get the 7E-05 instead of 0.00007. It's important to show to the client the 0.0007 value. – BlackM Aug 25 '15 at 13:31
  • 2
    Then format it when you show it to the client - in the report, app, Excel, whatever. I'm assuming your client is not running a SQL query? – D Stanley Aug 25 '15 at 13:38

1 Answers1

0

If you want to store values like this, use DECIMAL instead of FLOAT in following: DECIMAL(18,5) It will allow your to store number with 5 digits after ,

Sample:

CREATE TABLE #test
(
    ID DECIMAL(18,5)
)
INSERT INTO #Test VALUES (0.00007)

SELECT ID FROM #Test

DROP TABLE #Test

Result:

0.00007

As Jon Skeet said:

You're not actually trying to save 123.66, because you can't represent 123.66 exactly as a float or double. The database is saving the data more accurately than you're used to, that's all.

If you want to save decimal numbers accurately, use the decimal type.

You can read more at: Link 1 Link 2 Link 3 Link 4

Community
  • 1
  • 1
Stanislovas Kalašnikovas
  • 9,203
  • 4
  • 32
  • 59
  • 2
    The problem is not the data type - it's the _display_ of the data. `Decimal` has no better capability to store 0.00007 that `float` does – D Stanley Aug 25 '15 at 13:39
  • Hi and thanks for your answer.Actually numeric(18,5) solve this issue which I applied before posting the question here on a wrong field. So I applied on the correct now and it works ok. – BlackM Aug 25 '15 at 13:39
  • @DStanley yes, It is noticed in links which I provided. Here is solution how to store values like this `0.00007` (as `DECIMAL`) into table and after select It. – Stanislovas Kalašnikovas Aug 25 '15 at 13:49