CODEWITHSUNDEEP
sql-serversql-server-2014rounding
Boom
Boom

Reputation: 2825

SQL Server Round and Truncate Issue

So I've come across a strange issue in SQL Server with rounding.

Here's my example:

declare @amount float = 178.69999999

select
    @amount as [amount],
    round(@amount, 6) as [round],
    round(round(@amount, 6), 2, 1) as [trim_1],
    floor(round(@amount, 6) * power(10.0, 2)) / power(10.0, 2) as [trim_2]

And here's the result I get:

+--------------+-------+--------+--------+
|    amount    | round | trim_1 | trim_2 |
+--------------+-------+--------+--------+
| 178.69999999 | 178.7 | 178.69 | 178.7  |
+--------------+-------+--------+--------+

The general idea here is that I'm trying to round by 6 decimal places, then trim/floor/truncate by 2 decimal places. This means that I'm expecting a result of 178.7, but I'm getting a result of 178.69 for trim_1 (trim_2 is an alternative approach meant to yield the same result).

From what I can tell, I am using the round function appropriately, as the SQL Server documentation states:

Syntax

ROUND ( numeric_expression , length [ ,function ] )

function

Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

So I would expect trim_1 to match trim_2.

Here's the kicker: if I pass the result of round as a constant, and not as a variable, it works as expected:

select round(178.7, 2, 1) -- Yields 178.7

My guess is that SQL Server is doing something strange with floating points, or I've somehow managed to miss something. For what it's worth, I'm using SQL Server 2014, so perhaps that's my issue.

I'd like to get the result of trim_1 with as little code as possible, so that my end result looks cleaner.

Upvotes: 2

Views: 1376

Answers (1)

Simo
Simo

Reputation: 953

Use decimal instead of float.

Taken from Float and Real (Transact-SQL)

Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.

Replacing float with decimal in your code has the desired result:

declare @amount decimal(18, 10) = 178.69999999

select
    @amount as [amount],
    round(@amount, 6) as [round],
    round(round(@amount, 6), 2, 1) as [trim_1],
    floor(round(@amount, 6) * power(10.0, 2)) / power(10.0, 2) as [trim_2]

Results:

╔════════════════╦════════════════╦════════════════╦════════════╗
║     amount     ║     round      ║     trim_1     ║   trim_2   ║
╠════════════════╬════════════════╬════════════════╬════════════╣
║ 178.6999999900 ║ 178.7000000000 ║ 178.7000000000 ║ 178.700000 ║
╚════════════════╩════════════════╩════════════════╩════════════╝

Upvotes: 2

Related Questions