Menu
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
893
SQL Server ROUND() Function The ROUND() function rounds a number to a specified number of decimal places.
When rounding to the nearest hundred, look at the TENS DIGIT of the number. If that digit is 0, 1, 2, 3, or 4, you will round down to the previous hundred. If that digit is 5, 6, 7, 8, or 9, you will round up to the next hundred.
If you'd like to round a floating-point number to a specific number of decimal places in SQL, use the ROUND function. The first argument of this function is the column whose values you want to round; the second argument is optional and denotes the number of places to which you want to round.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
