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.
[sql]select round(12, -1)[/sql]
returns 10
[sql]select round(16, -1)[/sql]
returns 20
but length must be of type tinyint, smallint, or int; so you can’t do
[sql]select round(12, -0.5)[/sql]
to get 10
[sql]select round(16, -0.5)[/sql]
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.
[sql]select round((12*2), -1) /2[/sql]
returns 10
[sql]select round((16*2), -1) /2[/sql]
returns 15