Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Round real Value withoot truncation Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 02, 2009 3:49 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, October 27, 2009 5:48 AM Points: 19, Visits: 18
 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 isWhen n=2I want to round 18.005 to 18.01 or 18.0049 to 18.00(at 100th place)when i try with Declare @real realSet @real=18.005print 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
Post #746098
 Posted Thursday, July 02, 2009 4:45 AM
 Old Hand Group: General Forum Members Last Login: Monday, December 09, 2013 11:02 AM Points: 317, Visits: 400
 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.
Post #746126
 Posted Thursday, July 02, 2009 4:50 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 7:50 AM Points: 2,380, Visits: 3,358
 DECLARE @n TINYINTSET @n = 2DECLARE @Sample TABLE ( Value DECIMAL(15, 7) )INSERT @SampleSELECT 18.0049 UNION ALLSELECT 18.005SELECT 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"
Post #746132
 Posted Thursday, July 02, 2009 4:54 AM
 Old Hand Group: General Forum Members Last Login: Monday, December 09, 2013 11:02 AM Points: 317, Visits: 400
 Or do it the hardcore way like Peso's example
Post #746138
 Posted Thursday, July 02, 2009 5:32 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, October 27, 2009 5:48 AM Points: 19, Visits: 18
 Thanks for the Reply. I have already tried with converting the real into decimal before converting to VarcharDeclare @real realSet @real=Inputprint 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 19i can able to achieve the result when Input = 19.0051( Result is 19.01).
Post #746154
 Posted Thursday, July 02, 2009 11:34 AM
 SSC-Dedicated Group: General Forum Members Last Login: Yesterday @ 9:51 PM Points: 34,581, Visits: 28,764
Post #746503
 Posted Friday, July 03, 2009 12:06 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, October 27, 2009 5:48 AM Points: 19, Visits: 18
 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.
Post #746797
 Posted Friday, July 03, 2009 12:15 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 7:50 AM Points: 2,380, Visits: 3,358
 And that's the culprit. Due to the binary interpretation of REAL and FLOAT datatypes1 / 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 choices1. Keep REAL and learn to live with it2. Change to decimal and get what you want, all the times. N 56°04'39.16"E 12°55'05.25"
Post #746805
 Posted Friday, July 03, 2009 12:17 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, October 27, 2009 5:48 AM Points: 19, Visits: 18
 Hi. i can able to get what i am expected when i use the below codeDeclare @real Decimal(15,7)Set @real=19.005print ROUND(@real + CAST(0.5E * POWER(0.1E, 2) AS DECIMAL(15, 3)), 2, 1)Output = 19.01i 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
Post #746807
 Posted Friday, July 03, 2009 12:20 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 7:50 AM Points: 2,380, Visits: 3,358
 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"
Post #746809

 Permissions