• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

How do I round up to the nearest 100??

I'm using SQL to run some very in depth estimating formulas and need to be able to Round Up within the statement.  For instance, if the value my formula shoots out is 7122, I want that to be 7200.  

Round(7122,-2)  gives me 7100
Ceiling(7122) gives me 7122 (too bad ceiling doesn't take 2 arguements).

Any ideas?
1 Solution
use this

CEILING(7122 / 100.0) * 100
Aneesh RetnakaranDatabase AdministratorCommented:
declare @x int
set @x = 7100
SELECT case when @x%100 > 0 THEN  ((@x/100)+1 )*100 ELSE (@x/100)*100 end
drunk_irishmanAuthor Commented:
clever, thank you sir.
use this
i had to use a dummy table just ignore it
CEILING returns the smallest integer which is equal to or greater than the number it is given
the result on my example is the following
note just use ceiling(NUMBER/100)*100 in the example i show you all the results
select top 1 7122
     , 7122/100.0               as dividedby100
     , ceiling(7122/100.0)      as ceilingresult
     , ceiling(7122/100.0)*100 as myfinalnumber
  from gl20000

Open in new window


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now