SQL - script to round decimalup  to the nearest integer - SQL Server 2005

Posted on 2011-04-27
Last Modified: 2012-05-11
Hello experts,

I have a table with column "level" that holds decimal values.  I have another column called new_number, that I would like to have the level value rounded up to the nearest whole number when the decimal is equal to or greater than .5.  I would like to round down to the nearest whole number if the level value is less than .5:

I currently have:

table: person
level          new_number
1.6            null
0.5            null
11.9            null
8.2            null

and I would like to end up with:

table: person
level          new_number
1.6            2
0.5            1
11.9          12
8.2            8

Something like:

-- first for rounding up
update person
set new_number = dec(round(level / 1, 0)*1,11,0)
where right(level,2) >= .5

-- then for rounding down
update person
set new_number = dec(round(level / 1, 0)*1,11,0)
where right(level,2) < .5



Question by:robthomas09
    LVL 142

    Accepted Solution

    what about using the function CEILING ?
    LVL 29

    Assisted Solution

    by:Paul Jackson
    Use CEILING and FLOOR functions to roundup and rounddown

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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