SQL Server ROUND() not working for FLOAT ending in 1

Question

I changed my example to use FLOAT's ... too many people were getting hung up on the whole NVARCHAR thing. We are not storing numbers in the database as NVARCHAR. I was just using NVARCHAR as an example. I get the same results with FLOAT.

We're having issues with the SQL Server ROUND() for some FLOAT's

    DECLARE @EXCHANGE_RATE FLOAT
    SET @EXCHANGE_RATE = 1.327810000000000000000
    SELECT ROUND(@EXCHANGE_RATE,6,1)
    SET @EXCHANGE_RATE = 1.327820000000000000000
    SELECT ROUND(@EXCHANGE_RATE,6,1)

First one returns : 1.327809 Second one returns: 1.32782

You would think the first one would return 1.32781, and not 1.327809.

Is this a feature or bug in the ROUND? Any easy way around it?

Thanks!

Based on Mat's idea of casting them as DECIMAL, this works ... it's ugly, but works. The reason I use 10, is I looked in our database, and that seems to be the longest number we store. Also I need to convert it back to FLOAT because they don't want to see any trailing 0's

    DECLARE @EXCHANGE_RATE FLOAT
    SET @EXCHANGE_RATE = 1.327810000000000000000
    SELECT CAST(ROUND(CAST(@EXCHANGE_RATE AS DECIMAL(28,10)),6,1) AS FLOAT)

Answer 1

Try this query.

SELECT
  ROUND('1.3278100',6,0)                     AS x0,
  ROUND('1.3278200',6,0)                     AS y0,
  ROUND('1.3278100',6,1)                     AS x1,
  ROUND('1.3278200',6,1)                     AS y1,

  ROUND(CAST( '1.3278100' AS FLOAT), 6, 0)   AS a0,
  ROUND(CAST( '1.3278200' AS FLOAT), 6, 0)   AS b0,
  ROUND(CAST( '1.3278100' AS FLOAT), 6, 1)   AS a1,
  ROUND(CAST( '1.3278200' AS FLOAT), 6, 1)   AS b1,

  ROUND(CAST( '1.3278100' AS DECIMAL(9,8)), 6, 0)   AS i0,
  ROUND(CAST( '1.3278200' AS DECIMAL(9,8)), 6, 0)   AS j0,
  ROUND(CAST( '1.3278100' AS DECIMAL(9,8)), 6, 1)   AS i1,
  ROUND(CAST( '1.3278200' AS DECIMAL(9,8)), 6, 1)   AS j1
;

http://sqlfiddle.com/#!6/d41d8/15607

What this indicates to me is that ROUND() is implicitly casting your VARCHAR values to FLOAT before truncating them.

And as FLOATs can't accurately represent 1.327810, you're actually truncating 1.3278099999999 (or something similar).

So, in short, explicitly cast them to DECIMAL before you truncate. Or simply don't store numeric data as strings...