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