0

I have a table in sql server containing a float field. This field displays a different number of decimals from row to row. I know for a fact that a float field CAN NOT contain the exact value 3173.77, so some rounding is going on here below:

SELECT Weight ... FROM ...    WHERE ...
result : 
3173,77001953125
3173,77

but somehow sqlserverstudio somehow rounds to different lengths. Is this because a precision is stored ?

SELECT CAST(Weight AS DECIMAL(38,30)) ...  FROM ... WHERE ...
result : 
3173.770019531250000000000000000000
3173.769999999999981810105964541435

or is this because the second number in this case can round to 3173.77000000000000

2
  • Which version of SQL Server? SQL Server Management Studio or Azure Data Studio? Can you provide a table definition and sample data so people can try to reproduce what you're seeing and so offer a better explanation?
    – Paul White
    Commented Mar 24, 2023 at 0:17
  • FWIW, I typically show SELECT CAST(3.1 AS float) to my students and how SSMS and ADS returns "3,1" where SQLCMD returns 3.1000000000000001. I've always thought of this as rounding done by the tool but without any real insight or evidence to formalize those thoughts. Commented Mar 24, 2023 at 8:24

1 Answer 1

1

Data stored in floats are approximate by the nature of the datatype so precision doesn't apply here. What you're seeing when you convert your data is the approximate nature of the float datatype being made explicit by the decimal datatype.

If you want precision, stop storing your data as floats. That's really the only advice I can give you.

Not the answer you're looking for? Browse other questions tagged or ask your own question.