From: SnapDive on 8 Jun 2010 10:20 I have this value (it's a VarChar(36)): 9.981884727716366E4 If I cast it as Float and then Decimal(36,24) I get 0.000998188472771640000000 Note the rounding. I don't want ...64 I want ...6366 How can I make that happen? Thanks.
From: Eric Isaacs on 8 Jun 2010 13:52 The issue is that the FLOAT (and REAL) values are just fixed approximations of floating point/scientific notation numbers. When you try to cast or convert a scientific notation value to fixed number, you can lose some precision and accuracy, which is what you're experiencing. Also, Decimal(36,24) does not seem to hold the value anyway. Just try to assign the value to that number and you'll get an overflow error. You need more decimal precision, from what I tested. Here's a function you can use to convert the Scientific Notation value directly to DECIMAL without changing any precision. I would caution you though that your scientific notation values are probably not as accurate as you think, since they were scientific notation values in the beginning GO /* IF OBJECT_ID('dbo.jfn_ConvertScientificNotationToDecimal38_28', 'FN') IS NOT NULL DROP FUNCTION dbo.jfn_ConvertScientificNotationToDecimal38_28 */ IF OBJECT_ID('dbo.jfn_ConvertScientificNotationToDecimal38_28', 'FN') IS NULL BEGIN EXEC ('CREATE FUNCTION dbo.jfn_ConvertScientificNotationToDecimal38_28 () RETURNS INT AS BEGIN RETURN 1 END') END GO ALTER FUNCTION dbo.jfn_ConvertScientificNotationToDecimal38_28 ( @ScientificNotationValue VARCHAR(50) ) RETURNS DECIMAL(38,28) WITH SCHEMABINDING AS  BEGIN Function DECLARE @ReturnValue DECIMAL(38,28) DECLARE @Value VARCHAR(50) DECLARE @Exponent VARCHAR(10) DECLARE @CharIndex INT SET @CharIndex = CHARINDEX('E', @ScientificNotationValue) SET @Value = SUBSTRING(@ScientificNotationValue, 0, @CharIndex) SET @Exponent = '1' + SUBSTRING(@ScientificNotationValue, @CharIndex, LEN(@ScientificNotationValue)) SELECT @ReturnValue = CAST(@Value AS DECIMAL(36,28)) * CAST(CAST(@Exponent AS FLOAT) AS DECIMAL(36,28)) RETURN @ReturnValue END Function Eric Isaacs
From: Eric Isaacs on 8 Jun 2010 13:54 Here's a test script for that function... SELECT dbo.jfn_ConvertScientificNotationToDecimal38_28('9.981884727716366E4') AS ReturnValue Eric Isaacs
From: John Bell on 8 Jun 2010 14:33 On Tue, 8 Jun 2010 10:52:04 0700 (PDT), Eric Isaacs <eisaacs(a)gmail.com> wrote: >The issue is that the FLOAT (and REAL) values are just fixed >approximations of floating point/scientific notation numbers. When >you try to cast or convert a scientific notation value to fixed >number, you can lose some precision and accuracy, which is what you're >experiencing. Also, Decimal(36,24) does not seem to hold the value >anyway. Just try to assign the value to that number and you'll get an >overflow error. You need more decimal precision, from what I tested. > >Here's a function you can use to convert the Scientific Notation value >directly to DECIMAL without changing any precision. I would caution >you though that your scientific notation values are probably not as >accurate as you think, since they were scientific notation values in >the beginning > >GO >/* >IF OBJECT_ID('dbo.jfn_ConvertScientificNotationToDecimal38_28', 'FN') >IS NOT NULL > DROP FUNCTION dbo.jfn_ConvertScientificNotationToDecimal38_28 >*/ > >IF OBJECT_ID('dbo.jfn_ConvertScientificNotationToDecimal38_28', 'FN') >IS NULL > BEGIN > EXEC ('CREATE FUNCTION >dbo.jfn_ConvertScientificNotationToDecimal38_28 () RETURNS INT AS >BEGIN RETURN 1 END') > END >GO > >ALTER FUNCTION dbo.jfn_ConvertScientificNotationToDecimal38_28 >( > @ScientificNotationValue VARCHAR(50) >) >RETURNS DECIMAL(38,28) >WITH SCHEMABINDING >AS  >BEGIN Function > DECLARE @ReturnValue DECIMAL(38,28) > > DECLARE @Value VARCHAR(50) > DECLARE @Exponent VARCHAR(10) > DECLARE @CharIndex INT > > SET @CharIndex = CHARINDEX('E', @ScientificNotationValue) > SET @Value = SUBSTRING(@ScientificNotationValue, 0, @CharIndex) > SET @Exponent = '1' + SUBSTRING(@ScientificNotationValue, >@CharIndex, LEN(@ScientificNotationValue)) > > SELECT @ReturnValue = CAST(@Value AS DECIMAL(36,28)) * >CAST(CAST(@Exponent AS FLOAT) AS DECIMAL(36,28)) > > RETURN @ReturnValue >END Function > >Eric Isaacs Your exponent will never require decimal (36,28) and if you make this smaller then you won't need to change the precision. e.g. @ReturnValue = CAST(@Value AS DECIMAL(36,24)) * CAST(CAST(@Exponent AS FLOAT) AS DECIMAL(8,6)) John
From: Sylvain Lafontaine on 8 Jun 2010 15:27 I'm working on a similar problem. It now appears to me that even if float are stored internally with 16 digits, they are often converted to 15 digits when printed  including when using the str() function)  or otherwise converted to some other value such as Decimal (38,24). However, I've just found that the convert function can print all the 16 digits when using the style #2. With this style, a float value will always be printed on either 22 or 23 characters depending if it's positive or negative. This means that if you have a float value, you can use something like the following to convert it to a decimal value: declare @n varchar(30) set @n = '9.981884727716366E4' declare @f float set @f = convert (float, @n) declare @s as varchar(23) set @s = convert (varchar(23), @f, 2) declare @d decimal (36,24) set @d = convert (decimal (36,24), left (@s, len(@s)  5)) * convert (decimal (14,8), convert (float, '1' + right (@s, 5))) select @d  If you have the possibility of having to many digits, the computation  won't work, for example: set @d = convert (decimal (36,24), left (@s, len(@s)  5)) * convert (decimal (36,24), convert (float, '1' + right (@s, 5))) select @d  So you have to diminish the number of total digits in order to make it right: set @d = convert (decimal (36,24), left (@s, len(@s)  5)) * convert (decimal (18,12), convert (float, '1' + right (@s, 5))) select @d set @d = convert (decimal (32,24), left (@s, len(@s)  5)) * convert (decimal (28,18), convert (float, '1' + right (@s, 5))) select @d I don't really know what are the rules here. BTW, converting the exponent to an integer and use it to either multiply or divide the mantissa is probably the best way of doing while insuring of keeping the maximum number of digit. However, in your case, I'm not sure if you really want to convert your float values to decimal. When using big decimals between themselves, it doesn't take long to lose a lot of significant digits.  Sylvain Lafontaine, ing. MVP  Access Blog/web site: http://codingpaparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQLServer (French) "SnapDive" <SnapDive(a)community.nospam> wrote in message news:t9ks06pmbrjkg0j5ohoog0a8c8adh349m1(a)4ax.com... > > I have this value (it's a VarChar(36)): > > 9.981884727716366E4 > > If I cast it as Float and then Decimal(36,24) I get > > 0.000998188472771640000000 > > Note the rounding. I don't want ...64 I want ...6366 > > How can I make that happen? > > Thanks. > >

Next

Last
Pages: 1 2 Prev: Question about stored procedures  please advise. Next: Query question  Separating Columns? 