May 5, 2017 at 4:51 pm
Comments posted to this topic are about the item Round Natural Numbers to the nearest Power of 10
May 16, 2017 at 2:56 am
Why do you not use the build in ROUND function?
It takes three parameters:
- the value
- the rounding length (use negative numbers to round to a power of 10, example for 12345: -1 -> = 12350, -2 -> 12300, -3 = 12000
- optional 0 or 1, while the default 0 rounds, while 1 will cut the numbers off; ROUND(12345, -1, 0) = 12350
while ROUND(12345, -1, 1) = 12340
The only benefit of your function is the easier usage of the direction (up or down), but this could be solved by cutting off the decimals (= rounding down) and adding 10 (or 100 or 1000...) when you want to round up: ROUND(12345, -1, 1) + 10
PS: Bonus points for using an INLINE TABLE VALUE function instead of a scalar function
God is real, unless declared integer.
May 16, 2017 at 6:27 am
Your function has a problem when it's used against negative numbers. I wrote an article some time ago about this options which simulate CEILING and FLOOR. You can read it in here: http://www.sqlservercentral.com/articles/T-SQL/145448/
s
Here's a corrected version for your function
CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint, /*Natural Number*/
@RoundNearest float, /*Power of 10, 10^n , 10,100,1000...*/
@Direction int /* 0-> Down , 1 -> Up */
)
RETURNS TABLE AS
RETURN
SELECT CASE WHEN @Direction = 0
THEN ROUND(@Number-(.49*@RoundNearest),-LOG10(@RoundNearest))
ELSE ROUND(@Number+(.49*@RoundNearest),-LOG10(@RoundNearest))
END Number
GO
May 16, 2017 at 2:04 pm
Luis Cazares - Tuesday, May 16, 2017 6:27 AMYour function has a problem when it's used against negative numbers. I wrote an article some time ago about this options which simulate CEILING and FLOOR. You can read it in here: http://www.sqlservercentral.com/articles/T-SQL/145448/
s
Here's a corrected version for your function
CREATE FUNCTION dbo.udf_RoundNearest
(
@Number bigint, /*Natural Number*/
@RoundNearest float, /*Power of 10, 10^n , 10,100,1000...*/
@Direction int /* 0-> Down , 1 -> Up */
)
RETURNS TABLE AS
RETURN
SELECT CASE WHEN @Direction = 0
THEN ROUND(@Number-(.49*@RoundNearest),-LOG10(@RoundNearest))
ELSE ROUND(@Number+(.49*@RoundNearest),-LOG10(@RoundNearest))
END Number
GO
Thank you for your input!
May 16, 2017 at 2:04 pm
t.franz - Tuesday, May 16, 2017 2:56 AMWhy do you not use the build in ROUND function?
It takes three parameters:
- the value
- the rounding length (use negative numbers to round to a power of 10, example for 12345: -1 -> = 12350, -2 -> 12300, -3 = 12000
- optional 0 or 1, while the default 0 rounds, while 1 will cut the numbers off;ROUND(12345, -1, 0) = 12350
whileROUND(12345, -1, 1) = 12340
The only benefit of your function is the easier usage of the direction (up or down), but this could be solved by cutting off the decimals (= rounding down) and adding 10 (or 100 or 1000...) when you want to round up:
ROUND(12345, -1, 1) + 10
PS: Bonus points for using an INLINE TABLE VALUE function instead of a scalar function
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply