T-SQL Rouding, First Truncates to LENGTH + 2 : In using the T-SQL ROUND function I noticed what seems like weird behavior. It looks like the ROUND function only looks at the first digit to the right of the digit to be rounded. If I round -6.146 to one decimal I get -6.1. I would have thought it would start at the right and round each digit as it works its way to the left, like this: -6.146 -> -6.15 -> -6.2

I’ve observed the same behavior with Excel’s round function too.

The query below illustrates what I am describing. I may simply use the nested ROUND functions as shown below but I’m curious if there’s a better way and which approach is considered mathematically correct. 

DECLARE @Num AS FLOAT

SET @Num = -6.1463

SELECT @Num [OriginalVal], ROUND(@Num, 1, 0) [SingleRound], ROUND(ROUND(ROUND(@Num, 3, 0), 2, 0), 1, 0) [NestedRound]

Results

OriginalVal | SingleRound | NestedRound

-6.1463 | -6.1 | -6.2
