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)
9 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
Divide your column by 5, use a floor function, then multiply the result by again.
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
I’m finding that CEILING doesn’t work as expected for positive numbers below 1. Ex – SELECT CEILING(2/7) is returning 0.
Try this ..SELECT CEILING(CONVERT(Float,2)/CONVERT(Float,7))
select left(cast(600422.11759 – floor(600422.11759) as decimal(16,5)) * 100000,4)
will return 1175
Student can learn SEO Online from SeoWebChecker to get list of SQL related stuff in internet.
Thank you, you can find more on SeoWebChecker website.
When rounding 75.4966 to an integer you would expect 76, but round(75.4966,0) returns 75. So it only “looks” at the number after the decimal and not at the right most digit and round from right to left?