CODEWITHSUNDEEP
sqlsql-servercastingrounding
Daniel Cuesta González
Daniel Cuesta González

Reputation: 21

SQL Round cast to float

I have a problem with round in SQL Server 2014: when I round a number to 2 decimal places sometimes the rounded number is different if I cast to float before or not. For example, if I execute:

select round(cast(3.945 as float),2)
select round(3.945,2)

I have:

3.94
3.950

But if I execute:

select round(cast(3.935 as float),2)
select round(3.935,2)

I have:

3.94
3.940

It seems incorrect, rounding 3.935 and 3.945 casting to float before, I obtain the same value. Is this a bug?

Upvotes: 2

Views: 8330

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24410

As @ChrisTravers says in his answer the issue with rounding a float is that you're not getting exact arithmetic. i.e. That explains why round(3.945,2) rounds up to 3.95 whilst round(3.945E0,2) effectively rounds down to 3.94.

If you're wondering why you see more than 2 decimal places in some cases, that's because of the type you're dealing with. i.e. 3.94 is a float, so doesn't have a specified number of decimal places; whilst 3.950 is the result of rounding a decimal(4,3); which even though we've rounded to 2 decimal places doesn't affect the precision of the type (i.e. it's still decimal(4,3); not converted to decimal(4,2) or decimal(3,2)).

If the purpose of this rounding is for display purposes, you're best of using the str function. i.e.

  • select str(3.945,4,2) --decimal
  • select str(3.945E0,4,2) --float

In the above the 4 is the length of the string (i.e. includes the decimal point as a character), and the 2 is the number of decimal places to show. NB: In this scenario you're chaning the data type to varchar(4).

The below code allows you to see what type you get after performing an operation:

declare @result sql_variant = str(3.945E0,4,2)

select sql_variant_property(@result, 'BaseType') [BaseType]
,sql_variant_property(@result, 'MaxLength') [MaxLength]
,sql_variant_property(@result, 'Precision') [Precision]
,sql_variant_property(@result, 'Scale') [Scale]

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26454

The problem here is that float is a binary floating point type, where the representation is an approximation of the value. Floats do not losslessly convert to or from base 10, because there is no power of 10 that is also a power of 2. So when this is converted it is done in a way that leaves a roundoff error that pushes the value just before the rounding threshold.

Oddly I cannot reproduce the same behaviour on PostgreSQL and I am not entirely sure why (it may be that on PostgreSQL, round takes a numeric value and this forces a conversion back).

Never use floats where absolute accuracy is required. This occurs not only in databases, but in almost every programming language as well.

Upvotes: 2

Related Questions