SQL Rounding Up 2 Decimals Issue

December 23, 2022

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

>Solution :

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>