Rounding Up in SQL

Question

I would like to get the value of 50/60 as 1. However all the following returns 0

DECLARE @value decimal(10,2)
SET @value = 50/60
Select @value
SELECT ROUND(@value, 2) RoundNumber
SELECT CEILING(@value) CeilingNumber
SELECT FLOOR(@value) FloorNumber

Can you please advice?

Answer 1

you need to convert in decimal or make 50 to 50.0 which decimal

  DECLARE @value decimal(10,2)
SET @value = CEILING(50.0/60 )
Select cast (@value as int)

Answer 2

Consider the following script which explains the problem:

DECLARE @value DECIMAL(10,2)
SET @value = CAST(50 AS DECIMAL(10,2)) / CAST(60 AS DECIMAL(10,2));
SELECT @value                         -- 0.83
SELECT ROUND(@value, 2) RoundNumber   -- 0.83
SELECT CEILING(@value) CeilingNumber  -- 1
SELECT FLOOR(@value) FloorNumber      -- 0

The problem with your assignment to @value is that it first is doing integer division. This means that 50 / 60 will first evaluate to zero, due to integer truncation. Yes, after this, you are storing into a DECIMAL(10,2), but by then it is already too late, because you lost the decimal component.

In my suggestion above, I show that if you do division with proper decimals, then the calls to CEILING and FLOOR work as expected.