May 6, 2013 at 3:20 am
I would like to round up some figures with given precision. So far I can't find a way to do it. ROUND function doesn't work for me because it can't round up, CEILING doesn't work as well because the results is integer.
What I would like to achieve is a rounding up with give precision of 2 places after dot i.e.
5.0000016 should be 5.01
6.1000138 should be 6.11
7.1200073 should be 7.13
Is it any way to do it in SQL?
May 6, 2013 at 3:32 am
Maybe there is a better way, but here is one way:
declare @Num numeric(8,7)
set @Num = 0.0000001
SET @Num = CASE WHEN CAST(@Num AS NUMERIC(3,2)) = @Num THEN @Num ELSE CAST(@Num AS NUMERIC(3,2)) + 0.01 END
SELECT @Num
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2013 at 3:39 am
It works for me. Thank you very much.
May 6, 2013 at 5:25 am
Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2013 at 5:39 am
Jeff Moden (5/6/2013)
Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?
This would work as long as it is guaranteed that that each number will have a digit greater then zero in one of the 2 most left digits after the period (Hope that I wrote it correctly in English:-)). Here is an example:
--with both numbers the user wanted to get 0.01
select ROUND(0.0100000+.00499999,2,1)
select ROUND(0.0000001+.00499999,2,1)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 6, 2013 at 11:10 am
Here is another option (The CTE is just how I passed in the sample data):
with TestData as (
select datavalue
from (
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
)dt(datavalue)
)
select
datavalue,
(ceiling(datavalue * 100) * 1.0) / 100
from
TestData;
May 7, 2013 at 9:04 am
I came up with another solution based on the same mathematical concept as Lynn's, but I think Lynn's is more elegant:
with TestData as (
select datavalue
from (
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
)dt(datavalue)
)
select
datavalue,
(ceiling(datavalue * 100) * 1.0) / 100, -- Lynn's idea
CASE WHEN (datavalue * 100) % 1 > 0 THEN ROUND(datavalue, 2, 1) + 0.01 ELSE ROUND(datavalue, 2, 1) END -- My idea
from
TestData;
Jason Wolfkill
May 7, 2013 at 10:28 am
Here is a solution using the ROUND function:
with test_data_cte as
(
select datavalue
from
(
values
(5.0000016), -- should be 5.01
(6.1000138), -- should be 6.11
(7.1200073), -- should be 7.13
(5.0000000), -- should be 5.00
(6.1000000), -- should be 6.10
(7.1200000) -- should be 7.12
) dt (datavalue)
)
select
datavalue,
rounded_datavalue =
convert(numeric(8,2),round(datavalue+0.0049999,2))
from
test_data_cte
Results:
datavalue rounded_datavalue
--------------------------------------- -----------
5.0000016 5.01
6.1000138 6.11
7.1200073 7.13
5.0000000 5.00
6.1000000 6.10
7.1200000 7.12
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply