Round in MS SQL on 0.05 or 0.00

Question

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

Answer 1

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

Answer 2

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

Answer 3

Yes I miss something now when I was considering with my coworkers they said that we should round up prices, that mines that we are going to round 1,2,3,4,5 to five and 6,7,8,9 to zero +1. We have stock around 25 million Eur and 100K articles, Today I am going to make calculations on different rounding methods and I will post results to all can see what is can make effort on different kind of calculatinos. Anyway thanx a lot on fast response, so much faster then I can even and make calculations