How do I round up to the nearest 100??

Posted on 2010-01-05
Last Modified: 2012-05-08
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?
Question by:drunk_irishman
    LVL 26

    Accepted Solution

    use this

    CEILING(7122 / 100.0) * 100
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    declare @x int
    set @x = 7100
    SELECT case when @x%100 > 0 THEN  ((@x/100)+1 )*100 ELSE (@x/100)*100 end
    LVL 1

    Author Comment

    clever, thank you sir.
    LVL 5

    Expert Comment

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now