Round Up Number Integer in SQL query

user_12882636 0
Viewed 86

I have a query for round up for my stored procedure like this

DECLARE @number int;

SET @number = 918750;

SELECT @number + (100 - @number % 100 )

This returns 918800

The correct value to be returned would be 918,800.

But in case @number = 918800 it will change into 918900.

And I want only 2 last digit is round up.

Can I change it into single query only?

Yes if I use

SELECT ROUND(918750 , -2) AS RoundValue;

it return 918800, but if i use

SELECT ROUND(918740 , -2) AS RoundValue;

it return 918700, while i need is always round to up so the result i need is 918000

math rounding sql
3 Answers

if you re using any of Oracle, MySQL, and PostgreSQL, you can use ROUND() function.

like this

SELECT ROUND(918750 , -2)

This query will produce 918800 result. -2 means the last two digits are rounded up.

user_129649320

Try this

DECLARE @number int;

SET @number = 918750;
// Solution 1
select @number -(@number % 100)+iif(@number%100!=0,100,0);

//Solution 2
select ceiling(@number/100.00)*100;
user_70367500

Try this one

DECLARE @Number INT = 435667
SELECT CONVERT(INT, 100 * ROUND(@Number / 100.0, 0))
user_31039580
Related