Round real Value withoot truncation

Question

Round real Value withoot truncation

idrisgani

Valued Member

Points: 59
More actions
July 2, 2009 at 3:49 am

#155901

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Randolph West SSCommitted Points: 1585 More actions · Answer 1

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 SSC-Dedicated Points: 39757 More actions · Answer 2

DECLARE@n TINYINT

SET@n = 2

DECLARE@Sample TABLE

(

Value DECIMAL(15, 7)

)

INSERT@Sample

SELECT18.0049 UNION ALL

SELECT18.005

SELECTValue,

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 SSCommitted Points: 1585 More actions · Answer 3

Randolph West

SSCommitted

Points: 1585

July 2, 2009 at 4:54 am

#1019032

Or do it the hardcore way like Peso's example 🙂

idrisgani Valued Member Points: 59 More actions · Answer 4

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 SSC Guru Points: 1004339 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

idrisgani Valued Member Points: 59 More actions · Answer 6

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 SSC-Dedicated Points: 39757 More actions · Answer 7

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 Valued Member Points: 59 More actions · Answer 8

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 SSC-Dedicated Points: 39757 More actions · Answer 9

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"

idrisgani Valued Member Points: 59 More actions · Answer 10

Thanks for all the moderator,

At last i used the following code.

Round(Convert(Decimal(15,5),Convert(varchar(30),Convert(Decimal(15,7),@real))),2)

but the code is limited to a scale length of 5 and precision length of 15.

idrisgani Valued Member Points: 59 More actions · Answer 11

[Strike]Thanks for all the moderator,

At last i used the following code.

Round(Convert(Decimal(15,5),Convert(varchar(30),Convert(Decimal(15,7),@real))),2)

but the code is limited to a scale length of 5 and precision length of 15.[/Strike]

idrisgani Valued Member Points: 59 More actions · Answer 12

~~Thanks for all the moderator,~~

~~At last i used the following code.~~

~~Round(Convert(Decimal(15,5),Convert(varchar(30),Convert(Decimal(15,7),@real))),2)~~

~~but the code is limited to a scale length of 5 and precision length of 15.~~