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.

[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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.