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

Posted on 2011-04-27
Medium Priority
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35476293
what about using the function CEILING ?
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 1000 total points
ID: 35476355
Use CEILING and FLOOR functions to roundup and rounddown

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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