SQL – rounding to nearest 5

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

Leave a Reply

Your email address will not be published. Required fields are marked *