February 28, 2012 at 4:45 am
Hi ;
I want to round the last two numbers , below my query
DECLARE @N float
set @N=(SELECT CONVERT(numeric,(493-1)) / 493 *100)
PRINT @N
Result
99.7971
Expected Result
99.80
DECLARE @a Float
SET @a=(select CONVERT(numeric,(24625- 1 ))/24625 *100)
PRINT @a
Result
99.9959
Expected Result
99.99
In this two cases are solve the problems.
any one help this problems
February 28, 2012 at 4:58 am
In your first sample, you want to round 99.7971 to 99.80 which does look like a ROUND,
but in the second smaple, you have 99.9959 rounded to 99.99 which looks like a TRIM not a ROUND.
BTW, there is a ROUND function in SQLServer you can use...
February 28, 2012 at 3:53 pm
encapsulate your set logic with the ROUND() function.
set @N=ROUND((SELECT CONVERT(numeric,(493-1)) / 493 *100),2)
SET @a= ROUND((select CONVERT(numeric,(24625- 1 ))/24625 *100),2)
-You results will be
99.8
100
However, as stated in the previous reply, your statement is round on one and trim another.
You can do that by
DECLARE @a varchar(10)
SET @a= (select CONVERT(numeric,(24625- 1 ))/24625 *100)
Select SUBSTRING(@a,1,(Charindex('.',@a)+2))
February 29, 2012 at 3:08 am
Isn't it much simpler to just specify the number of decimals in the convert operation? i.e. simply:
select convert(numeric(10,2), 99.9959), convert(numeric(10,2),99.7971)
which returns:
--------------------------------------- ---------------------------------------
100.00 99.80
Because indeed, rounding 99.9959, results in 100.00, not 99.99.
You can use the round() operator to 'trim' a number too. If you specify the 3rd optional parameter as a non-zero value, the input number will be truncated after the n-th position, instead of rounded.
select round(99.9959, 2, 1)
which returns:
---------------------------------------
99.9900
February 29, 2012 at 4:34 am
DECLARE @N float
DECLARE @a Float
set @N=(SELECT CONVERT(numeric,(493-1)) / 493 *100)
PRINT @N
PRINT cast(@N as numeric(18,2))
--Result
--99.7971
--Expected Result
--99.80
SET @a=(select CONVERT(numeric,(24625- 1 ))/24625 *100)
PRINT @a
PRINT cast(@a as numeric(18,2))
PRINT cast(18.24 as numeric(18,2))
--Result
--99.9959
--Expected Result
--99.99
Regards
Guru
March 1, 2012 at 8:50 am
Hi, i like to use this operation. But i must admit it migth be litle costly:
select ROUND( 100 * (99.7971), 0) / 100
March 1, 2012 at 3:50 pm
adrian.facio (3/1/2012)
Hi, i like to use this operation. But i must admit it migth be litle costly:select ROUND( 100 * (99.7971), 0) / 100
But why would you do that? the 2nd parameter of round() is used to specify the position where to round. So you can leave off, 100 * and the /100 and specify ", 2" instead of ", 0" to get exactly the same result. i.e.:
ROUND( 99.7971, 2)
March 2, 2012 at 7:42 am
It works for rounding to 8ths, 16th, 32 ths, 7ths, anyways any "th", this case our friend needed to round to 100ths
March 2, 2012 at 7:44 am
Is just a different aproach that is useful for different scenarios too.
March 2, 2012 at 7:46 am
for example : round( 8 * Value) / 8 rounds to the nearest 8th. By the way, i'm sorry for posting several replys, the ideas came to me in batches he he.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply