Had a requirement to round some price values to the nearest £5 in a SQL stored proc.
It’s easy to round to the nearest 10 using a negative value for the length parameter of the ROUND function i.e.
select round(12, -1)
returns 10
select round(16, -1)
returns 20
but length must be of type tinyint, smallint, or int; so you can’t do
select round(12, -0.5)
to get 10
select round(16, -0.5)
to get 15
What you can do is a little manipulation……
double the value first, then round to the nearest 10, then halve the value, i.e.
select round((12*2), -1) /2
returns 10
select round((16*2), -1) /2
returns 15