Prev: Question about stored procedures - please advise.
Next: Query question - Separating Columns?
From: SnapDive on 8 Jun 2010 10:20

I have this value (it's a VarChar(36)):

9.981884727716366E-4

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.981884727716366E-4')
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.981884727716366E-4'

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://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"SnapDive" <SnapDive(a)community.nospam> wrote in message
news:t9ks06pmbrjkg0j5ohoog0a8c8adh349m1(a)4ax.com...
>
> I have this value (it's a VarChar(36)):
>
> 9.981884727716366E-4
>
> 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?