Want to disable rounding in SSMS settings

Question

Is there a way to disable numeric rounding for the query results in my SSMS settings? For example, my query returns a value of 2.6. This is misleading because when I export the results to a flat file, the value is 2.55555555555. I would like to know this what my client will see on the file I send them so that I can fix it in the query.

I am using Windows 10 Enterprise OS Build 19044.2130 64 bit & SSMS v18.12.1.

Answer 1

To adjust the behaviour, try this query in SSMS:

select format(NumericValue, 'G17')  
from ███████  

Do not use the format function in SSIS if not needed.

Answer 2

Here is an example. The NumericValue field is a float data type. I am trying to just cut the value off after the first decimal place without rounding it. So the end result for this record should be 6.5.

I added a CAST AS VARCHAR to my CASE. I also tried moving this query to a CTE that feeds only the casted varchar values into my larger query, but get the same results.

My query results show everything looking fine, but this is the value going to my flat file.

Answer 3

In addition to other answers:

Float is an approxamite data type. Or more precisely it consists of a mantissa of 53 bits and an exponent. Converting this value to decimal can in most cases not be done exactly. And the same goes for converting to string.

There are more than one library for converting float to string. SSMS uses .NET, which is less zealous with showing decimal. In SSIS, you can select which API to use, but I believe OLE DB is the most common, and this is native code. This library is more prone to show extra decimals.