Rounding-up TSQL

问题:

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?

回答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)

回答2:

Change it for :

select CEILING(85/30.0)

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

回答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