Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Round real Value withoot truncation


Round real Value withoot truncation

Author
Message
idrisgani
idrisgani
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 22
Hi,
I want to perform the following steps
1. Convert the real number into a character.
2. Convert the resulting character to a decimal.
3. Round the value at the N+1 th place.

My requirement is
When n=2
I want to round 18.005 to 18.01 or 18.0049 to 18.00(at 100th place)

when i try with

Declare @real real
Set @real=18.005
print Round(Convert(Decimal(15,7),Convert(varchar(30),@real)),2)

i can able to get the result until i get some indefinite real value like -4.8892562E-08. so when i try the same logic, i am getting a conversion error(Error converting data type varchar to numeric.). Please help me to get the problem resolved. thanks in advance
Randolph West
Randolph West
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 431
You're going to run into that problem because of the rule for the floating point. How about converting to decimal(38,17) from real before trying to cast it out to a varchar?

Alternatively, do this in code.
SwePeso
SwePeso
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 3431

DECLARE @n TINYINT

SET @n = 2

DECLARE @Sample TABLE
(
Value DECIMAL(15, 7)
)

INSERT @Sample
SELECT 18.0049 UNION ALL
SELECT 18.005

SELECT Value,
ROUND(Value + CAST(0.5E * POWER(0.1E, @n) AS DECIMAL(15, 7)), @n, 1)
FROM @Sample




N 56°04'39.16"
E 12°55'05.25"
Randolph West
Randolph West
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 431
Or do it the hardcore way like Peso's example :-)
idrisgani
idrisgani
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 22
Thanks for the Reply. I have already tried with converting the real into decimal before converting to Varchar

Declare @real real
Set @real=Input
print Round(Convert(varchar(30),Convert(Decimal(15,7),@real)),2)

If Input = 19.005, expected Result is 19.01. but i cant able to reproduce the same. Actual result is 19
i can able to achieve the result when Input = 19.0051( Result is 19.01).
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51795 Visits: 40308
idrisgani (7/2/2009)
Thanks for the Reply. I have already tried with converting the real into decimal before converting to Varchar

Declare @real real
Set @real=Input
print Round(Convert(varchar(30),Convert(Decimal(15,7),@real)),2)

If Input = 19.005, expected Result is 19.01. but i cant able to reproduce the same. Actual result is 19
i can able to achieve the result when Input = 19.0051( Result is 19.01).



Based on that reply, I'm thinking that you didn't try Peso's code which actually does work. Don't assume that just reading the code will be your answer. Try it. ;-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
idrisgani
idrisgani
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 22
Hi Peso, Thanks for the code.
Your logic works well when the input type is of decimal. but my input type is real.it is rounded to 19 and not 19.01, when the input type is of real.
SwePeso
SwePeso
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 3431
And that's the culprit. Due to the binary interpretation of REAL and FLOAT datatypes

1 / 2^n + 1 / 2^(n+1) + 1 / 2^(n+2) + 1 / 2^(n+3) + ...

the value may not be stored exactly as you want. So you are left with two choices

1. Keep REAL and learn to live with it
2. Change to decimal and get what you want, all the times.


N 56°04'39.16"
E 12°55'05.25"
idrisgani
idrisgani
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 22
Hi. i can able to get what i am expected when i use the below code

Declare @real Decimal(15,7)
Set @real=19.005
print ROUND(@real + CAST(0.5E * POWER(0.1E, 2) AS DECIMAL(15, 3)), 2, 1)
Output = 19.01

i am not able to predict the length of the digit after dot, it may vary. so the length of the decimal used while casting should not be hard-coded.

i cant able to supply the length at runtime like decimal(15,@n+1).

Thanks,
Idris Gani R
SwePeso
SwePeso
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2731 Visits: 3431
print cast( ROUND(@real + CAST(0.5E * POWER(0.1E, 2) AS DECIMAL(15, 3)), 2, 1) AS decimal(15, 2))


N 56°04'39.16"
E 12°55'05.25"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search