SQL Server ROUND () does not work for FLOAT ending in 1

Question

I changed my example to using FLOAT ... too many people hanged themselves on the whole NVARCHAR thing. We do not store numbers in a database like NVARCHAR. I just used NVARCHAR as an example. I get the same results with FLOAT.

We are having problems with SQL Server ROUND () for some FLOAT

  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 returns: 1.327809 Second returns: 1.32782

You think that the first one will return 1.32781, not 1.327809.

Is this a sign or error in ROUND? Any easy way around this?

Thank!

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

    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 request.

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

This indicates that it ROUND()implicitly throws your values VARCHARin FLOATbefore truncating them.

FLOAT 1.327810, 1.3278099999999 ( - ).

, , DECIMAL , . ...

Answer 2

, , op.
- , MatBailie, - nVarChar float, ...

MSDN

. tinyint, smallint int. 0 ( ), . * , 0, . *

, :

DECLARE @EXCHANGE_RATE NVARCHAR(200)
SET @EXCHANGE_RATE = '1.3278100'
SELECT ROUND(@EXCHANGE_RATE,6,0) Rounded,
       ROUND(@EXCHANGE_RATE,6,1) Truncated

SET @EXCHANGE_RATE = '1.327810000001'
SELECT ROUND(@EXCHANGE_RATE,6,0) Rounded,
       ROUND(@EXCHANGE_RATE,6,1) Truncated

... "1.3278100", , (' 1.3278100 ' 5 ), (6,5) - ... . .

Answer 3

ROUND(@var,6) ROUND(@var,6,1). ROUND() , , .