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)