# How to Round Up or Round Down Number in SQL Server? – Interview Question of the Week #125

Question: How to Round Up or Round Down Number in SQL Server? Answer: This is a very popular question. Let me rephrase this question as I often see the other version of this question being asked as well.

Alternate Version of Question: How to find ceiling or floor value for any number?

Well, here is the quick answer.

```DECLARE @value decimal(10,2)
SET @value = 50.516171
SELECT ROUND(@value, 2) RoundNumber
SELECT CEILING(@value) CeilingNumber
SELECT FLOOR(@value) FloorNumber```

Let us see the answer of the above query. You can clearly see that with the help of Ceiling and Floor function, we are able to get the nearest integer for any value on either side.

Please note this is very different from the function round. The function round either use ceiling or floor logic under the hood and gives us nearest integer and it is very different from the other number.

Here is another article which I wrote for SQL Server 2012 where I discussed the summary of the analytic functions. Please click here to read Summary of All the Analytic Functions.

Another interesting function which was introduced in SQL Server 2016 was String_Split. You can click here to read more about the function String_Split.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

#### Related Posts

• Dhrumil
October 31, 2018 3:58 pm

I want to round down to nearest to 5 multiples E.g. 20.00 to 24.99 would display 20 and 25.00 to 29.99 would display 25

• big
November 27, 2018 8:11 pm

Divide your column by 5, use a floor function, then multiply the result by again.

• neha
May 2, 2019 3:56 pm

I want to round down to Higher to 5 multiples E.g. 20.01 to 25 and 29.5 to 30.00 .
If its Greater then tens by 0.1 i.e., 10.01 then also display would display 15.
and 25.01 to 30.
Is am Using
1) SET @Length= ‘1642’;
SET @Length2 = (CAST(@Length / 5 AS INT) + IIF(CAST(ROUND(@Length, 0) AS INT) % 5 > 1, 1, 0)) * 5;
print @Length2;
Its Displaying 1645
2)SET @Length1= ‘2230.5’;
SET @Length4 = CEILING(@Length1)
print @Length4;
SET @Length3 = (CAST(@Length4 / 5 AS INT) + IIF(CAST(ROUND(@Length4, 0) AS INT) % 5 > 1, 1, 0)) * 5;
print @Length3;
Its should display 2235 but Displaying 2230
3) SET @Length= ‘1641’;
SET @Length2 = (CAST(@Length / 5 AS INT) + IIF(CAST(ROUND(@Length, 0) AS INT) % 5 > 1, 1, 0)) * 5;
print @Length2;
Its Displaying 1640 but should display 1645

• Stacy
January 10, 2021 10:59 pm

I’m finding that CEILING doesn’t work as expected for positive numbers below 1. Ex – SELECT CEILING(2/7) is returning 0.

• 