views:

56

answers:

4

Is it possible to easily round a figure up to the nearest 100 (or 1000, 500, 200 etc.) in SQL Server?

So:

720 -> 800
790 -> 800
1401 -> 1500

+1  A: 

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

SELECT ROUND(@value/100) * 100

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

krock
A: 

There's no native function that will do this, but there are any number of simple math tricks that will. An example:

DECLARE @Foo int
SET @Foo = 720

print @Foo
print (@Foo + 100) % 100
PRINT @Foo - (@Foo + 100) % 100
Philip Kelley
A: 

You can use this code, assuming your amount is an int. If not you will need to cast, so you get integer division.

If amount % 100 != 0 Then
   roundedAmount = ((amount / 100) * 100) + 100
Else
   roundedAmount = amount

You might want to package this into a user defined function.

C. Ross
+4  A: 

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
Gray