CODEWITHSUNDEEP
sqlsql-serverrounding
Apis
Apis

Reputation: 103

SQL Rounding to 2 decimals places Issue

I have a problem with the SQL output. Some of the outputs after rounding obtain the correct value, but some output with the same value after rounding off are getting an incorrect value. Is there anyone facing this kind of problem?

Here's my SQL

select Line, amount, round(amount,2) amount2 from xxx

And this is the output, you can see that the amount is same for Line 1 and 2 but the round value is different.

Line    amount    amount2
1        6.525       6.52
2        6.525       6.53
3        6.525       6.52
4        2.175       2.18
5       41.325      41.33
6        6.525       6.52
7        2.175       2.17
8       19.575      19.58
9        6.525       6.53
10       2.175       2.18
11      41.325      41.33
12       2.175       2.17
13       2.175       2.18
14       2.175       2.18
15       2.175       2.17
16      36.975      36.97
17       6.525       6.53
18       6.525       6.53
19      19.575      19.58
20       6.525       6.52
21      36.975      36.98
22       2.175       2.18
23       2.175       2.18
24      19.575      19.57
25       2.175       2.18
26       2.175       2.18
27       2.175       2.17
28       2.175       2.17
29       2.175       2.18
30      41.325      41.32
31       2.175       2.18

Upvotes: 2

Views: 2212

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272006

This can happen if your decimal-like values are in fact floats. Here is a repro:

DECLARE @test TABLE (line INT, amount FLOAT);
INSERT INTO @test VALUES
(1, 6.525),
(2, 6.524999999999999);

SELECT line, amount, FORMAT(amount, 'G17') AS dotnet_g17_formatted, ROUND(amount, 2) AS amount2
FROM @test

Result:

| line | amount | dotnet_g17_formatted | amount2 |
|------|--------|----------------------|---------|
| 1    | 6.525  | 6.5250000000000004   | 6.53    |
| 2    | 6.525  | 6.5249999999999986   | 6.52    |

You can see that float values are stored as an approximation and displayed as such.

The most appropriate solution is to store financial values as DECIMAL.

Upvotes: 3

Michał Turczyn
Michał Turczyn

Reputation: 37337

I can't reproduce your error, as my SQL rounds values consistently :) But you can force some consistent behaviour on SQL, try this query (you can choose from three possibilities):

select line, amount, 
       round(amount, 2, 1) truncated, --third parameter, if it's other than 0, makes round function to truncate value
       ceiling(amount * 100) / 100 roundUp,
       floor(amount * 100) / 100 roundDown
from @tbl

You could write custom rounding using methods to round up and round down I provided.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use cast to covert it in decimal then round it

select Line, amount, round(cast(amount as decimal(10, 2),2) amount2 from xxx

Upvotes: 0

Related Questions