60

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

sth
  • 222,467
  • 53
  • 283
  • 367
joshcomley
  • 28,099
  • 24
  • 107
  • 147

17 Answers17

71

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
  • 115,027
  • 24
  • 293
  • 354
  • I need something if C# to do the same thing, and the formula above works well. The only slight problem is that does not work if X is a negative. The way I fixed it (not entirely sure of SQL syntax, but should be easy to figure out) was to make sure X is always positive (Abs(X)) and then multiply by -100. – Nigel Ellis May 30 '14 at 08:37
  • 2
    Sorry to say, but this answer only seems to work for integer values. X=100.25 still gives 100 instead of rounding up to 200. @Krock's ceiling answer works better for floats & decimals – bmadtiger Jun 26 '17 at 02:52
  • Answer by @rkgit seems much better. I came searching for answer for question 10 in https://napier.sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial and the one by rkgit generates accepted answer. That answer is round(YourValue, -3) – Atul Kumar Aug 22 '20 at 05:34
62

For rounding Up to the nearest thousand, try the following:-

select round(YourValue, -3)
Sabito stands with Ukraine
  • 4,271
  • 8
  • 34
  • 56
rkgit
  • 777
  • 5
  • 3
34

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

SELECT CEILING(@value/100.0) * 100

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

krock
  • 28,904
  • 13
  • 79
  • 85
  • This doesn't always round *UP*, as specified in the question. For example `SELECT ROUND(129.0/100, 0) * 100` returns 100 instead of 200. – Gyromite Aug 15 '16 at 17:13
  • @Gyromite strange that the website I linked to was correct, but I must have copied it wrong :/ – krock Aug 15 '16 at 23:46
  • Can you explain why are you dividing and multiplying with 100? Also whats the reason you took ceil over floor @krock – terry Apr 27 '19 at 21:52
  • 3
    @terry The dividing and multiplying here is just based on math, the only real way to understand this is perhaps by example. Ceiling works because it rounds UP to nearest int. Here's a quick example: You want to round 149 to 200. 149/100 = 1.49, ceiling will round that to the nearest INT, which is 2. Then you multiply 2 by 100 and viola you get 200. This math concept works on ANY fixed interval you want to round to. If you want to round UP to nearest 8: 149/8=18.625, ceiling that to 19, then 19x8 = 152. 152 is the nearest interval of 8 rounded up! :) – LittleTreeX May 22 '20 at 13:48
9

Use CEILING function to round a figure up

DECLARE @Number DECIMAL, @RoundUp DECIMAL
SET @RoundUp = 100
SET @Number = 720
SELECT CEILING(@Number/@RoundUp)*@RoundUp
VMAtm
  • 27,943
  • 17
  • 79
  • 125
Shri
  • 91
  • 1
  • 2
4

Try this:

select round(@value , -2);

Saumya
  • 87
  • 8
  • 1
    This doesn't always round *UP*, as specified in the question. For example `select round(129.0 , -2);` returns 100 instead of 200. – Gyromite Aug 15 '16 at 17:14
4

It is very simple to round a number to any multiple of nearest 10 by using simply the ROUND function for ex: 

SELECT ROUND(number/1000,2)*1000

This will give you the nearest thousandth value.

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
Sanjeet
  • 37
  • 3
1

This will work for the values with decimal also.

select floor((ceiling (@value) + 99) / 100) * 100;

steave
  • 203
  • 1
  • 6
  • 9
1

It works fine for integer value:

@roundUpValue = ((@intValue / 1000) + 1) * 1000
@roundDownValue = (@intValue / 1000) * 1000

For example

declare @intValue as int = 1934
select ((@intValue / 1000) + 1) * 1000 as roundUp
select (@intValue / 1000) * 1000 as roundDown

If you want to round up to the nearest 500 then

select ((@intValue / 500) + 1) * 500 as roundUp
jorlinski
  • 111
  • 2
0

A generic solution - Use MOD to find the last 100th place and then add 100 to the result.

select (720 - MOD(720,100)) + 100 from dual;

If you need the next 80th place, just replace any "100" with "80".

Shaulian
  • 411
  • 5
  • 8
  • There's problem with this formula when then input value is already a multiple of 100. `select (800 - (800 %100)) + 100;` will give you 900 instead of 800. – Gyromite Aug 15 '16 at 17:21
0

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
  • 39,426
  • 11
  • 57
  • 92
  • This doesn't always round *UP*, as specified in the question. You example with 720 as the input should return 800, but instead it returns 700. – Gyromite Aug 16 '16 at 14:29
0

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
  • 31,137
  • 42
  • 147
  • 238
0

In addition to Gray's answer, I'd use the following inline function:

CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint,
@RoundNearest bigint,
@Direction int
)

RETURNS TABLE AS 
RETURN

SELECT CASE WHEN @RoundNearest>=@Number THEN @Number
       ELSE
        (
                (@Number + CASE 
                           WHEN @Direction = 0 --Round Down
                           THEN 0
                           ELSE CASE WHEN @Number % @RoundNearest = 0 THEN 0 ELSE @RoundNearest END
                           END) / @RoundNearest) * @RoundNearest

   END Number

Parameter Definition:

  1. @Number - the number you need to round
  2. @RoundNearest 10th, 100th , 1000th etc
  3. @Direction 0-> round down, 1-> round up

using the function:

SELECT * FROM dbo.udf_RoundNearest (1965,100,1) --> 2000
SELECT * FROM dbo.udf_RoundNearest (1359,100,0) --> 1300
SELECT * FROM dbo.udf_RoundNearest (1999,10,0) --1990
SELECT * FROM dbo.udf_RoundNearest (80,100,0) --> 80 (if the @number parameter is less or equal the @RoundNearest parameter the result will be the @number itself

it can also be used as apply it versus a table such as:

;with tmp (Value) as
  (select 1236 union all select 6584 union all select 9999)

    select t.*, fn.Number
    from tmp t
    cross apply dbo.udf_RoundNearest (Value,100,0) fn

    /*Result Set
    Value   Number
    1236    1200
    6584    6500
    9999    9900*/
Community
  • 1
  • 1
hkravitz
  • 1,345
  • 1
  • 10
  • 20
0

This worked fine for me.

Round(@value/100, 0) * 100

CRV
  • 69
  • 8
0
Select round(value/100,0)*100

Whatever number you want to round to, just post that instead of 100 here.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Yuvansh Garg
  • 1
0

i have create a function in mssql it can help you

CREATE function dbo.roundup ( @numbr decimal(18,2), @frac decimal(18,2) ) RETURNS decimal(18,2) AS BEGIN DECLARE @result decimal(18,2) set @result = ceiling(@numbr/@frac)*@frac RETURN @result END GO

AlMounkez
  • 67
  • 3
0

try this code

round(yourvalue , -2)

Explaination=> when we mentioned -ve in round funtion it rounds the values and make zero Example=>round(1238283.9827398 ,-2) gives output=>1238300.0000000

Lokesh Rajpoot
  • 1
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 19 '23 at 01:18
0
  • SELECT ROUND(Number,-2) for rounding nearest hundredth position
  • SELECT ROUND(Number,-3) for rounding nearest thousandth position
Prakash Thapa
  • 1