Rounding-up TSQL

Question

I gotta be missing something obvious.

select CEILING(85/30) = 2

85/30 = 2.83333

I want the value to be 3.

Shouldn't the CEILING function round up for me?

Solution 1

Try

SELECT CEILING(85.0/30)

And for comparison

SELECT 85.0 / 30, 85 / 30

The first example uses floats, the second uses ints, so the result is rounded before the ceiling function is hit. What you do is

SELECT CEILING(2) 

Rather than

SELECT CEILING(2.833333)

Solution 2

Change it for :

select CEILING(85/30.0)

INT / INT yields an INT, so 85/30 rounds it down (FLOOR).

Solution 3

Use some typed-parameters, and you won't have to worry so much about how you enter the data. Here's a sam

DECLARE @int_num integer
DECLARE @int_dem integer
DECLARE @dec_num decimal(18,0)
DECLARE @dec_dem decimal(18,0)

SET @int_num = 85
SET @int_dem = 30
SET @dec_num = 85
SET @dec_dem = 30

SELECT CEILING(@int_num / @int_dem) AS int_div, CEILING(@dec_num / @dec_dem) AS dec_div

int_div   |   dec_div
----------------------
2         |   3