SQL Server - Rounding off issue

Question

Below is the part of code that I am using in a SQL Server stored procedure.

DECLARE @MinimumTime FLOAT,
        @filter VARCHAR(MAX)

SET @MinimumTime = 43885.664166666664241

SELECT @filter =  COALESCE('[Time.Minimum] >= ' + CAST(@MinimumTime AS varchar(MAX)), '')
PRINT @filter

This produces this output:

[Time.Minimum] >= 43885.7

which is not the expected one and impacting output of the procedure. I don't want this numerical value to be rounded off. I want the complete float value I have passed. Something like below.

[Time.Minimum] >= 43885.664166666664241

Please let me know if you need any additional information.

The fact that you have a variable called `Filter` where you're trying to create the value `'[Time.Minimum] >= 43885.664166666664241'` point **very** strongly to you're injecting values into a dynamic statement. Seems like an [XY Problem](http://xyproblem.info). Why do you need a dynamic statement, and why are you **parametrising** it? — Thom A, Feb 25 '20 at 11:30
Wanting "the complete value" for a floating-point value is fraught with peril since you are already dealing with a rounded value when parsing the constant. `SELECT FORMAT(@MinimumTime, 'G38')` will give you as much digits as SQL Server can give you, but even that will only show `43885.664166666669`, which cannot be distinguished from `43885.664166666664241` (after rounding). You will get another (different) value if you leverage `PARSE`, as that uses .NET code -- `SELECT FORMAT(PARSE('43885.664166666664241' AS FLOAT), 'G38')` gives `43885.664166666662`. — Jeroen Mostert, Feb 25 '20 at 11:32
Casting either value to `BINARY(8)` will show you the difference between these is literally the smallest possible (`0x40E56DB540DA740D` vs. `0x40E56DB540DA740E`) so you're not going to get the results you want using a `FLOAT`. Use a string, a `DECIMAL`, a parameter if you have an exact value from somewhere else, or change your expectations. Remember that *any* character representation of a floating-point value introduces a representation issue before you even get down to parsing it. — Jeroen Mostert, Feb 25 '20 at 11:34

score 3 · Answer 1 · answered Feb 25 '20 at 11:30

Use parameters! That is the only way to get an "exact" float value:

declare @sql nvarchar(max);

set @sql = N'
select *
from t
where '[Time.Minimum] >= @MinimumTime
';

exec sp_executesql @sql,
                   N'@MinimumTime float',
                   @MinimumTime=@MinimumTime;

In other words, don't convert to a string.

score 1 · Accepted Answer · answered Feb 25 '20 at 11:36

Please, try with below query from float to numeric SQL-FIDDLE:

DECLARE @MinimumTime numeric(32,15),
@filter VARCHAR(MAX)
SET @MinimumTime = 43885.664166666664241

SELECT @filter =  COALESCE('[Time.Minimum] >= ' + cast(@MinimumTime as varchar(MAX)), '')
select @filter

Note:

The basic difference between Decimal/Numeric and Float :
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Thanks all for answers, most of them are working for me but this one will require least change in existing proc. I hope this doesn't have any negative impact on the performance. — KnowledgeSeeeker, Feb 25 '20 at 11:44

score 1 · Answer 3 · answered Feb 25 '20 at 11:37

1

You can also use

CONVERT

SELECT @filter =  COALESCE('[Time.Minimum] >= ' + CONVERT(NVARCHAR(30), @MinimumTime,2), '')

But this will give you the output in scientific notation.

answered Feb 25 '20 at 11:37

Naveen Kumar

1,988
1
7
11

SQL Server - Rounding off issue

3 Answers3