I have a huge table of product rows... I only need a small portion of its data, more specifically the prices of the products (regular price - for which I have to choose between two fields in the sense that if one is present, I pick it, otherwise I pick the other; and sale price - which for many products is stored as a float with three decimals, because it was calculated as a percentage of the regular price). So I crafted the appropriate query to achieve what I want, and noticed a very strange behavior for the ROUND() function.

In some cases, when the third decimal digit is 5 (ie. .165) is truncated to .16 and in others it's rounded up to .17, and this happens for any other number with 5 at the third decimal place as well of course! How can that be possible? Here is the query:

SELECT CODE, FWHSPRICE, RTLPRICE, CASE WHEN ISNULL(FWHSPRICE, 0) = 0 THEN RTLPRICE ELSE FWHSPRICE END AS REGULAR, ROUND(FLDFLOAT3, 2) AS SALE
FROM MATERIAL
WHERE COMID = 12
AND FLTID1 = 1

And here is a screenshot of a comparison between the two recordsets, on the left without ROUND() in the query, and on the right with ROUND()

PS: If you want me to export data for replication, can you please explain to me how to create the appropriate INSERT statements for you? The whole table has so many fields - and rows, and I don't know how to set SSMS to do that. I'm coming from MySQL, so this "realm" of SQL Server is so new to me... Thank you in advance.

CodePudding user response：

Yeah, you're mixing two things that have their own sets of quirky behavior (IMHO). I would honestly just not use float unless I needed the specific properties of float, but if you're stuck with this data type...

I would first convert from float to decimal with an extra decimal place (or maybe even 2), then use another convert to round instead of round itself. For example:

DECLARE @x TABLE(x float);

INSERT @x(x) VALUES(0.615),(0.165),(0.415),(0.414);

SELECT 
  x, 
  bad    = ROUND(x, 2), 
  better = CONVERT(decimal(10,2), CONVERT(decimal(10,3), x))
FROM @x;

Results:

• Example db<>fiddle

If you have values like 0.4149, you can see how an extra decimal place will prevent that from rounding up (unless that's the behavior you want):

DECLARE @f float = 0.4149;

SELECT source     = @f,
       round_up   = CONVERT(decimal(10,2), CONVERT(decimal(10,3), @f)),
       round_down = CONVERT(decimal(10,2), CONVERT(decimal(10,4), @f));

Results: