SQL, round to the nearest whole number

Hello all,

How to round to the nearest whole number in SQL
e.i.
.03   = 1
1.03 = 2
if there is a remaining i want to add 1.
Thanks
LVL 1
frimyAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
Pawan KumarDatabase ExpertCommented:
Please try this

/*------------------------
SELECT CEILING(.03) 
UNION ALL
SELECT CEILING(1.03) 
------------------------*/

---------------------------------------
1
2

(2 row(s) affected)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Another solution for you.

/*------------------------

SELECT CASE WHEN CAST(a AS FLOAT) LIKE '%.%' THEN CAST(1 + a AS INT) ELSE a END 
FROM
(
	SELECT (.03) a
	UNION ALL
	SELECT (1.03) 
	UNION ALL
	SELECT 2 
	UNION ALL
	SELECT 5 
)r
------------------------*/

---------------------------------------
1.00
2.00
2.00
5.00

(4 row(s) affected)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
David Johnson, CD, MVPOwnerCommented:
Actually strictly speaking 1.03 rounded to the nearest whole number is 1 not 2. For this one usually uses bankers rounding
int (value + .45)
0
 
Pawan KumarDatabase ExpertCommented:
@Author - did u tried my suggestions, both are giving exactly same output you need. Thanks
0
 
frimyAuthor Commented:
Thanks
I wasn't sure what ceiling does.
it works fine
have a nice day!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.