SQL, round to the nearest whole number

frimy
frimy used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this

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

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

(2 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Top Expert 2016

Commented:
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)
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - did u tried my suggestions, both are giving exactly same output you need. Thanks

Author

Commented:
Thanks
I wasn't sure what ceiling does.
it works fine
have a nice day!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial