SQL Server Rounding Issue Where There Is 5

SQL Server Rounding Issue where there is 5

If you do want to use banker's rounding in SQL Server...

CREATE FUNCTION BankersRounding(@value decimal(36,11), @significantDigits INT)        
RETURNS MONEY        
AS        
BEGIN        
    -- if value = 12.345 and signficantDigits = 2...        

    -- base = 1000        
    declare @base int = power(10, @significantDigits + 1)        

    -- roundingValue = 12345        
    declare @roundingValue decimal(36,11) = floor(abs(@value) * @base)        
    -- roundingDigit = 5        
    declare @roundingDigit int = @roundingValue % 10        

    -- significantValue = 1234        
    declare @significantValue decimal(36,11) = floor(@roundingValue / 10)        
    -- lastSignificantDigit = 4        
    declare @lastSignificantDigit int = @significantValue % 10        

    -- awayFromZero = 12.35        
    declare @awayFromZero money = (@significantValue + 1) / (@base / 10)        
    -- towardsZero = 12.34        
    declare @towardsZero money = @significantValue / (@base / 10)        

    -- negative values handled slightly different        
    if @value < 0        
    begin        
        -- awayFromZero = -12.35        
        set @awayFromZero = ((-1 * @significantValue) - 1) / (@base / 10)        
        -- towardsZero = -12.34        
        set @towardsZero = (-1 * @significantValue) / (@base / 10)        
    end        

    -- default to towards zero (i.e. assume thousandths digit is 0-4)        
    declare @rv money = @towardsZero        
    if @roundingDigit > 5        
        set @rv = @awayFromZero  -- 5-9 goes away from 0        
    else if @roundingDigit = 5         
    begin        
        -- 5 goes to nearest even number (towards zero if even, away from zero if odd)        
        set @rv = case when @lastSignificantDigit % 2 = 0 then @towardsZero else @awayFromZero end        
    end        

    return @rv        

end

Sql server round interpreting 5 as lower

You can use this. It will work for you.

DECLARE @test decimal(10,3) = 1.055
SELECT CASE WHEN round(@test,3,1) - round(@test,2,1) = 0.005 THEN round(@test,2,1) ELSE round(@test,2) END

SQL Server Rounding Issue

ROUND() is working as it was intended to. You specified to round to 2 decimal places, and that's what you got.

Returns a numeric value, rounded to the specified length or precision.

Rounding means that a digit of 5 or above goes up to nearest, less than 5 down to nearest.

so,

PRINT ROUND(1890.125000, 2)

produces 1890.130000

Whereas

PRINT ROUND(1890.124999, 2)

produces 1890.120000

T-SQL Rounding Issue

If you requirement is that all of the results have to add up to the original value (common for calculating taxes), you just can't round each value.

You need to look into rounding of previous calculation and round up or down depending on how previous rounding worked.

For calculation with 2 percentages following formula will work:

declare @amount2 smallmoney = 6.16
declare @percent1 decimal(5,2) = 56.25
declare @percent2 decimal(5,2) = 43.75

select cast((@amount2 * @percent1)/100  AS NUMERIC(36,2))
--select cast((@amount2 * @percent2)/100  AS NUMERIC(36,2))
select cast(@amount2 - cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) AS NUMERIC(36,2))

select cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) + cast(@amount2 - cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) AS NUMERIC(36,2)) -- The total here 6.17. I need 6.16

TSQL number rounding issue

In the expression FLOOR(5.7456 * 1000 + 0.4);, the part between parentheses is evaluated first. For constants the data types are inferred based on the notation; for 5.7456 that is decimal(5,4); 1000 is an int; and 0.4 is decimal(1,1). The inferred data type for 5.7456 * 1000 is then decimal(10,4); and for the full expression it is decimal(11,4). These are all exact numeric data types so you will not experience any rounding; the end result is 5746.0000 exactly. The FLOOR function trims the fraction and converts to decimal(11,0), returning 5746.

In the user-defined function, you store input parameters and intermediate results in float data type (floating point data). This data type is intended to be used for approximate data, such as measurements, where the data you read from the intstrument is already an approximation. I have learned in high school to read as many digits as I can, but treat the last one as insignificant - I had to keep it in all computations, but round the final result to the number of significant digits based on the accuracy of my measurements. The rounding ensures that inaccuracies in the last digits will not affect the end result.
Floating point data types should be treated in the same way.

Internally, floating point digits are represented in a base-2 number system. This means that there are numbers that have an exact representation in our commonly used base-10 system (such as 5.7456), but a never ending fractional part in base-2. (Similar to how for instance one third, which can be represented exactly in base-3, has a never ending fractional part in base-10: 0.33333333333(etc)). The number of base-2 digits used for storage of a float number is finite, so it has to be cut off at the end - which results in it being rounded either up or down by a tiny fraction. You can see this if you run:

DECLARE @a float = 5.7456;
SELECT CAST(@a AS decimal(19,16));

In this case, the effect of cutting off after a lot of base-2 digits is that the value stored is 0.0000000000000004 less than the decimal value you put in. That small difference turns into a huge effect because of the FLOOR function, which does exactly what it should do: round down to the nearest integer.

(I've seen a lot of people call this an error. It is not. It is intended and documented behavior. And the precision loss here is neither worse nor better than the precision loss you get when you store a third in a DECIMAL(7,6); it is just a bit less obvious because we have all grown up being used to working in base-10)

Rounding off incorrectly in SQL

Well, the values are just at the brink. 0.425 and 0.625 would get rounded up, while for instance 0.42499999999999 and 0.62499999999999 would get rounded down.

You are using FLOAT, which is not a precise data type, but an approximate one. You can hence only expect approximate results.

If you want exact results, use an exact type like DECIMAL.

Docs: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15

Sql Round, when on .5, rounds to the greater number ie 1.235 result 1.24

Maybe you can decrease the amount a litle bit before rounding, like this

Select Round(TestData-.001,2) From TestTableRound

Another trick (for your case) is to multiply by 100, take the integer part, then divide by 100

Select 
 CASE WHEN FLOOR(TestData * 1000)-FLOOR(TestData * 100)*10 <= 5 THEN
          FLOOR(TestData * 100)/100
      ELSE ROUND(TestData , 2)
 END 
 From TestTableRound

Sql server rounding issue down to 2 decimal places

Try this

select cast(round(@num/@vat,3) as decimal(18,2))

Round to nearest 5 in SQL Server

select round(FineAmount*2,-1)/2 from tickets

or to put nicholaides suggestion in sql

select round(FineAmount/5,0)*5 from tickets

The example assumes that FineAmount is of type money.
The second approach is probably better as the first one works with the limit of maximum_value_of_money_type/2