CODEWITHSUNDEEP
sql-servermathrounding
joshcomley
joshcomley

Reputation: 28838

Round *UP* to the nearest 100 in SQL Server

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

Upvotes: 66

Views: 161204

Answers (17)

Prakash Thapa
Prakash Thapa

Reputation: 1

  • SELECT ROUND(Number,-2) for rounding nearest hundredth position
  • SELECT ROUND(Number,-3) for rounding nearest thousandth position

Upvotes: 0

Lokesh Rajpoot
Lokesh Rajpoot

Reputation: 1

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

Upvotes: 0

AlMounkez
AlMounkez

Reputation: 77

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

Upvotes: 0

Yuvansh Garg
Yuvansh Garg

Reputation: 1

Select round(value/100,0)*100

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

Upvotes: 0

rkgit
rkgit

Reputation: 827

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

select round(YourValue, -3)

Upvotes: 67

jorlinski
jorlinski

Reputation: 111

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

Upvotes: 1

CRV
CRV

Reputation: 69

This worked fine for me.

Round(@value/100, 0) * 100

Upvotes: 0

steave
steave

Reputation: 223

This will work for the values with decimal also.

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

Upvotes: 1

hkravitz
hkravitz

Reputation: 1385

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*/

Upvotes: 0

Sanjeet
Sanjeet

Reputation: 37

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.

Upvotes: 4

krock
krock

Reputation: 29619

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.

Upvotes: 35

Saumya
Saumya

Reputation: 87

Try this:

select round(@value , -2);

Upvotes: 4

Shaulian
Shaulian

Reputation: 427

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

Upvotes: 0

Shri
Shri

Reputation: 91

Use CEILING function to round a figure up

DECLARE @Number DECIMAL, @RoundUp DECIMAL
SET @RoundUp = 100
SET @Number = 720
SELECT CEILING(@Number/@RoundUp)*@RoundUp

Upvotes: 9

Gray
Gray

Reputation: 116908

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

Upvotes: 75

C. Ross
C. Ross

Reputation: 31848

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.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40359

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

Upvotes: 0

Related Questions