问题:

Hello I am coming from Bosnia and Herzegovina and in our county the smallest note bill is 0.05, Now the government pushing us to our retrial prices rounding on 0.05 or at 0.00. Therefor I want to create SQL Scalar Valued Function for rounding the prices on given value. Is there some build in solution so I can save resource of my queries. Thanx in advice Best regards

Edit from comment:

0,1,2,3,4 go to zero
5,6,7,8,9 going to zero+1

回答1:

Thanks to marc_s, i changed to money datatype.

float vs decimal vs money datatype article and flowchart

ALTER FUNCTION dbo.ufnRound (@number money)
RETURNS money
AS
BEGIN
    RETURN FLOOR(@number*20) / 20
END
GO
SELECT dbo.ufnRound (1.22), dbo.ufnRound (1.23), dbo.ufnRound (1.27), dbo.ufnRound (1.28)

-- gives    1.2    1.2   1.25   1.25

回答2:

There's nothing built-in - but it's easy enough to create:

EDIT : adapted to your special rounding needs :-)

create function dbo.RoundToFiveOrZero(@inputValue MONEY)
returns MONEY
as begin
  return FLOOR((@inputValue + 0.05) * 20.0) / 20.0
end

Use the DECIMAL or MONEY data type - whatever suits you best.

Use it like this:

select 
    dbo.RoundToFiveOrZero(1.51),
    dbo.RoundToFiveOrZero(1.53),
    dbo.RoundToFiveOrZero(7.33),
    dbo.RoundToFiveOrZero(7.37),
    dbo.RoundToFiveOrZero(7.39)

Which gives you 1.50, 1.50, 7.30, 7.40, 7.40 as results.

Marc