T-SQL – Rounding Floats

Back in 2018, I wrote this post on converting floats to string values. I wouldn’t choose to define a float data type, but in some cases you’ll have to work with them.
Recently, I’ve run into a situation at work where I needed to round float values, so I needed to refresh my knowledge on rounding numbers in T-SQL.

We’ll define a float value, and run it through a few different methods of rounding:

DECLARE @Number FLOAT = 12345.6789;

SELECT 
	@Number AS [Number],
	ROUND (@Number, 2) AS [Round2],
	ROUND (@Number, 2, 1) AS [Round21],
	CAST(@Number AS DECIMAL(7,2)) AS [Cast],
	STR(@Number, 8, 2) AS [STR],
	FORMAT(@Number, 'N2') AS [Format];

The ROUND function can work with most numeric values; int. decimal, float, money. Since we’re passing in a float, we’ll get a float back.
ROUND (x, 2) will round the x value off to 2 decimal values.
ROUND (x, 2, 1) will also round the x value off to 2 decimal values. The third value will determine how the value is rounded. With 0, the normal rounding rules that you would expect will be followed. For any non-zero value, the value is truncated. In looking at the results table, you’ll see that ROUND(x, 2, 1) rounded the decimal portion to .67, instead of the .68 that you would expect normally.

There’s also the option to CAST the value to a decimal. Since we’ve defined the decimal as having two decimal places, the input value will automatically be rounded.

The last two examples will convert the float to a string value.
The STR function takes the number to round, the number of characters to return, and the number of decimal places to return. Notice that we have to specify 8 in order to get back the 7 digit number, since the period will count as a character in the return value.
With the FORMAT function, we can use N2 as the format value, which means a numeric value rounded to two places. Since I’m using US culture values, the return value will have a comma separator as well.

So the ROUND function gives a little more control over the result. But if we want to convert to a decimal or to a string value, we can round in the same step.