Round to nearest 5 in SQL Server

Question

Hello,

I have a Money column in my SQL Server 2008 table. In my below query how can I round it to nearest 5$

select FineAmount from tickets

Thanks

Answer 1

A general math solution:

Divide by 5, round to the nearest integer, then multiply by 5.

Answer 2

Use ROUND function

    SELECT  ROUND(FineAmount,5)
    FROM    tickets

Answer 3

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

More on ROUND