2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lass
  • 149
  • 1
  • 7

3 Answers3

2

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.

ahuemmer
  • 1,653
  • 9
  • 22
  • 29
ikbalkazanc
  • 161
  • 1
  • 8
  • 1
    indeed, if i input SELECT ROUND(918740 , -2) . it returns 918700, i need it always round up and the result should be 918800 – lass Oct 30 '21 at 13:02
  • I used postgresql. so its generated 918800. if you use another db, result can be diffrent. I'm not entirely sure about that. – ikbalkazanc Oct 31 '21 at 04:11
2

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;
Prasanna Kumar J
  • 1,288
  • 3
  • 17
  • 35
0

Try this one

DECLARE @Number INT = 435667
SELECT CONVERT(INT, 100 * ROUND(@Number / 100.0, 0))
Mohsen.es
  • 1
  • 4