Round *UP* to the nearest 100 in SQL Server

Solution 1:

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

Solution 2:

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

select round(YourValue, -3)

Solution 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.

Solution 4:

Use CEILING function to round a figure up

DECLARE @Number DECIMAL, @RoundUp DECIMAL
SET @RoundUp = 100
SET @Number = 720
SELECT CEILING(@Number/@RoundUp)*@RoundUp

Solution 5:

It is very simple to round a number to any multiple of nearest 10 by using simply the ROUND function for ex:

SELECT ROUND(number/1000,2)*1000 

This will give you the nearest thousandth value.