SQL Rounding Up 2 Decimals Issue

Question

I am attempting to use a TSQL script to update a large table of values on a particular date. When I attempt this by a set multiplier, the number are not rounding up correctly when 3 decimal places. Why is this not rounding up to 75.23 from 75.225 for example

Declare @value float = 50.15
Declare @multiplier float = 1.5

select
@value * @multiplier FloatResult,
cast(@value * @multiplier as decimal(10,2)) RoundedAttempt

You can see the result is 75.225 and this needs to be a money value but it is not rounding up to 75.23 and is returning as 75.22. Help please, thank you

Please note I have attempted the SQL of calculating this as

Declare @value float = 9.90
Declare @multiplier float = 1.5

select
    @value * @multiplier FloatResult,
CEILING(@value * @multiplier * 100) / 100 RoundedResult

But this Ceiling calculation returns the result as £14.86 when it should be £14.85

You can try SELECT CEILING(@value * @multiplier * 100) / 100 AS RoundedResult — User 123732, Dec 23 '22 at 17:23
Hi, thank you, I did try this but that fails as well if you change the value to be multiplied to 9.90 for example. this should multiply up to 14.85. But this method comes back as £14.86, which is wrong unfortunately — user2364055, Dec 23 '22 at 17:26
Declare your types as decimals not floats. Floats are for imprecise values usually extremely large or small numbers where precision doesn't matter or is insignificant compared to "storage" . When you're dealing with precision; you need to use decimal values. Consider: https://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net — xQbert, Dec 23 '22 at 17:27
Don't use a `float` and this isn't an issue. The value you have can't be represented accurately in a base 2 value, and so it's actual value is not quite what you think it is. The closest you can store `75.225` in a `float` is `75.22499847412109375` which when rounded to 2 decimal places is `75.22`. — Thom A, Dec 23 '22 at 17:27
Thank you, this works perfectly. I have tested with both scenarios and this returns it correctly. Simple as that, thank you all — user2364055, Dec 23 '22 at 17:33

score 2 · Accepted Answer · answered Dec 23 '22 at 17:33

Use DECIMALs:

See the difference:

D:\TEMP>sqlcmd
1> Declare @value decimal(10,2) = 9.90
2> Declare @multiplier decimal(10,2) = 1.5
3>
4> select
5>     @value * @multiplier FloatResult,
6> CEILING(@value * @multiplier * 100) / 100 RoundedResult
7> go
FloatResult             RoundedResult
----------------------- ---------------------------------
                14.8500                         14.850000

(1 rows affected)
1> Declare @value float = 9.90
2> Declare @multiplier float = 1.5
3>
4> select
5>     @value * @multiplier FloatResult,
6> CEILING(@value * @multiplier * 100) / 100 RoundedResult;
7> go
FloatResult              RoundedResult
------------------------ ------------------------
      14.850000000000001       14.859999999999999

(1 rows affected)
1>

SQL Rounding Up 2 Decimals Issue

1 Answers1