How to round median value

Question

I have problem, I cannot the fix bug with scalar-valued function. I have next function:

(@list varchar(max))
returns float
as
begin

    declare @Median float
    select @Median = (
    convert(float,(SELECT MAX(value) FROM
    (SELECT TOP 50 PERCENT value from split(@list,',') order by value) BottomHalf))
    +
    convert(float,(SELECT MIN(value) FROM
    (SELECT TOP 50 PERCENT value from split(@list,',') order by value DESC) TopHalf))
    ) / 2
    option (maxrecursion 0)

    return  (ROUND (@Median ,2))

end

In the response, I get for example: 1.155. when I add return (ROUND (1.155 ,2)) I get 1.15 but I need 1.16. I try add ROUND(CAST ( 1.155 AS float ),2) but it not helped me

Answer 1

Presumably, your return value is more like 1.154999999. This will round down. If you need to still get 1.16, then try declaring @median to have three decimal places:

declare @median decimal(18, 3)

I question whether this is what you really need. But here is a SQL Fiddle.