Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Round real Value withoot truncation Expand / Collapse
Author
Message
Posted Thursday, July 2, 2009 3:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:57 AM
Points: 19, Visits: 19
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
Post #746098
Posted Thursday, July 2, 2009 4:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:18 PM
Points: 317, Visits: 408
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 2, 2009 4:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 2,397, Visits: 3,408
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"
Post #746132
Posted Thursday, July 2, 2009 4:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 9:18 PM
Points: 317, Visits: 408
Or do it the hardcore way like Peso's example
Post #746138
Posted Thursday, July 2, 2009 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:57 AM
Points: 19, Visits: 19
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).

Post #746154
Posted Thursday, July 2, 2009 11:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 35,218, Visits: 31,677
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #746503
Posted Friday, July 3, 2009 12:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:57 AM
Points: 19, Visits: 19
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 3, 2009 12:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 2,397, Visits: 3,408
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"
Post #746805
Posted Friday, July 3, 2009 12:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 9:57 AM
Points: 19, Visits: 19
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
Post #746807
Posted Friday, July 3, 2009 12:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:50 AM
Points: 2,397, Visits: 3,408
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
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse