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?

How to Round Up or Round Down Number in SQL Server? - Interview Question of the Week #125 ceiling-floor1-800x213

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.

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

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)

, , ,
Previous Post
How to Insert Results of Stored Procedure into a Temporary Table? – Interview Question of the Week #124
Next Post
How to Add Column at Specific Location in Table? – Interview Question of the Week #126

Related Posts

3 Comments. Leave new

  • 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

    Reply
  • 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

    Reply

Leave a Reply

Menu