SQL Server Rounds FLOATS when Casting to VARCHAR

A quirk about converting between the FLOAT and the VARCHAR data types in SQL Server is the unexpected rounding that will happen. This is specific to working with the FLOAT data type, and the rounding will not happen when casting a DECIMAL or an INTEGER.

The way to fix this is to use the STR( function to convert your FLOAT rather than CAST( or CONVERT( . STR was pretty much built for the purpose of displaying FLOATs as is in string format.

Below I’ll give the results of using the STR function to cast a FLOAT, after which I’ll give examples of using CAST on a FLOAT, a DECIMAL, and an INT data type.

STR( FLOAT to VARCHAR

DECLARE @MyNumber FLOAT
 SET @MyNumber = 12345.4867
 SELECT STR(@MyNumber,10,4)

Result: 12345.4867 (OK)

Here we use the STR( function to get our VARCHAR, which works quite well. Additionally, a nice feature of STR is that if you increase the number of decimal places past the number of places that your FLOAT has, the STR function will add zeros. For example:

DECLARE @MyNumber FLOAT
 SET @MyNumber = 12345.48
 SELECT STR(@MyNumber,10,4)

Result: 12345.4800 (OK)

If you set the number of decimal places to less than the decimal places in the FLOAT, then SQL Server will round your number to that number of decimal places. If you want the number truncated instead, you should use a string manipulation.

DECLARE @MyNumber FLOAT
 SET @MyNumber = 12345.4867
 SELECT STR(@MyNumber,10,2)

Result:   12345.49 (OK, but note the rounding that happened)

CAST FLOAT to VARCHAR

The CAST( operation always seems to round to the nearest tenths, regardless of the number of decimal places that the FLOAT was set with.

DECLARE @MyNumber FLOAT
 SET @MyNumber = 12345.48
 SELECT CAST(@MyNumber AS VARCHAR(100))

Result: 12345.5 (Fail)

Now trying the same with a FLOAT with a number of decimal places, you can see that it still rounds to the same decimal place:

DECLARE @MyNumber FLOAT
 SET @MyNumber = 12345.4867
 SELECT CAST(@MyNumber AS VARCHAR(100))

Result: 12345.5 (Fail)

CAST DECIMAL to VARCHAR

DECLARE @MyNumber DECIMAL(10,2)
 SET @MyNumber = 12345.48
 SELECT CAST(@MyNumber AS VARCHAR(100))

Result: 12345.48 (OK)

CAST INTEGER to VARCHAR

DECLARE @MyNumber INTEGER
 SET @MyNumber = 123
 SELECT CAST(@MyNumber AS VARCHAR(100))

Result: 123 (OK)