SQL Question: ROUND() didn't go to the same school I did?

I had an email from a friend today where he asked what was wrong with SQL Server, and wondered if ROUND() didn’t go to the same school that he did.

The example was:

SELECT '0.5' [SOURCE VALUE], '1' [SHOULD ROUND TO], ROUND('0.5',0) [SQL SAYS]
UNION
SELECT '1.55', '1.6', ROUND('1.55',1)
UNION
SELECT '94.55', '94.6', ROUND('94.55',1);

The output was:

What was puzzling him is why SQL Server’s ROUND was converting 94.55 to 94.5 and not 94.6. Now writing numbers as strings is problematic to start with, but that aside, he was puzzled by the output.

Rounding

There isn’t just one “correct” way to do rounding. Here is info on it: https://en.wikipedia.org/wiki/Rounding

The T-SQL ROUND() function does apply “standard” mathematical rounding. Values from 5 and up in the next digit go to the next value, less than 5 goes down. So you’d expect 94.55 to be 94.6 not 94.5.

It’s worth noting that not all languages do this. Some languages implement Bankers’ Rounding. This was designed to avoid the skew that you can get if you have a whole lot of .5 values. For example, 2.5 + 3.5 + 4.5 would be 3 + 4 + 5 = 12 if you rounded them all to integers first. With Bankers’ rounding, it alternates. 0.5 goes to 0, 1.5 goes to 2, 2.5 also goes to 2, 3.5 goes to 4, etc. And so the 2.5 + 3.5 + 4.5 would be 2 + 4 + 4 which is 10, and closer to the underlying total of 10.5.

Older versions of VB, etc. used Banker’s Rounding, but that’s not what the T-SQL ROUND() function does.

So what’s up with ROUND() ?

In that case, what’s the problem with his original query?

The issue is data types. He didn’t provide decimal values to round, he provided strings. That means they have to go to another data type before they are rounded. Which one?

If I execute this code, you might see the problem:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.GregTemp;

SELECT '94.55' AS SourceValue, 
       '94.6' AS ShouldRoundTo, 
       ROUND('94.55',1) AS RoundedValue
INTO dbo.GregTemp;

SELECT * FROM dbo.GregTemp;

SELECT c.[name] AS ColumnName, t.[name] AS DataType
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON t.system_type_id = c.system_type_id
AND t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.GregTemp');

DROP TABLE IF EXISTS dbo.GregTemp;

as it returns this:

Note that the value is a float. And as I’ve talked about in this blog many times, floats are evil for business applications, and a common mistake.

To see the difference, instead of using the implicit conversion, try this:

SELECT '94.55' AS SourceValue, 
       '94.6' AS ShouldRoundTo, 
       CAST('94.55' AS decimal(18, 1)) AS RoundedValue
INTO dbo.GregTemp;

SELECT * FROM dbo.GregTemp;

SELECT c.[name] AS ColumnName, t.[name] AS DataType
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON t.system_type_id = c.system_type_id
AND t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.GregTemp');

DROP TABLE IF EXISTS dbo.GregTemp;

And that returns this output:

Note that the rounded value is the expected value, and the output data type is the expected decimal type.

Finally, also keep in mind what when SQL Server Management Studio shows you a float value, it also rounds it before it shows it to you. So a value like 9.9999999999999999 might appear as 10.0 but it’s not 10.0.

Hope this helps someone.

2023-07-11