CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014reporting
Qasim Qasimov
Qasim Qasimov

Reputation: 9

SQL Server 2014 rounding a float gives unexpected result

Could anyone explain the result of the following query? I think this is a bug in SQL Server 2014.

DECLARE @x float
SET @x=58.415
SELECT ROUND(58.415,2), ROUND(@x,2)

enter image description here

Upvotes: 0

Views: 473

Answers (2)

gotqn
gotqn

Reputation: 43626

Because the first argument is stored as decimal(5,3):

EXEC sp_describe_first_result_set N'SELECT 58.415 x', null, 0;

You have two different codes:

DECLARE @x float 
SET @x=58.415 
SELECT ROUND(58.415,2), ROUND(@x,2)

GO

DECLARE @x decimal(19,3)
SET @x=58.415 
SELECT ROUND(58.415,2), ROUND(@x,2)


GO

Basically, the float is

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

An improvement made by @Zohar explaining why the value is converted to decimal:

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

The explanation for what you are seeing is that floating point arithmetic is not exact in SQL Server (or in any other database or programming language). Here is what is actually happening, with the "real" value being shown for explanation:

SELECT
    ROUND(58.415, 2),           -- rounds UP to 58.420, this is DECIMAL(10,3), EXACT
    ROUND(58.4149999999999, 2)  -- rounds DOWN to 58.41

The issue here is that when you made the following variable assignment:

DECLARE @x float
SET @x = 58.415

internally, SQL Server actually stored the value as an approximation, something like 58.41499999999. Then, when rounding to two decimal places, you were left with 58.41.

In general, if you require exact precision, you should use an exact type. In this case, DECIMAL(10,3) would work.

Upvotes: 1

Related Questions