Round UP to the nearest 100 in SQL Server

The following should work. After reading your question, I'm not exactly sure what you want 100 to return. For this 100 returns 100.

select floor((X + 99) / 100) * 100;

This gives the following results:

0 -> 0
1 -> 100
99 -> 100
100 -> 100
101 -> 200

For rounding Up to the nearest thousand, try the following:-

select round(YourValue, -3)

One option would be to use the CEILING() function like this:

SELECT CEILING(@value/100.0) * 100

You may need to convert your value to a decimal first depending on its type.

Related