Problem converting numbers from scientific fromat

Question

Post reply

Problem converting numbers from scientific fromat

RonyA

SSC Veteran

Points: 270
More actions
May 25, 2005 at 6:51 am

#163837

Hi,
I have a query which returns a float like 8.5164835164835168E-2.
This value is paased to another application which displays 3 digits precision number, by trimming the right part of the number (leaving only the 5 left 'chars').
In this case, my application displays 8.516, istead of 0.085.
I tried to use cast or convert to numeric(12.3) or decimal, and then, 8.5000000000000006E-2 is displayed.
Does anyone have a solution?
Is there any way to avoid using scientific notation in the SQL server?

Thanks in advance,
Rony

Viewing 15 posts - 1 through 15 (of 15 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

Scale and precision is just something I choose at random, not optimized, but this seems to work..?

select cast(8.5164835164835168E-2 as decimal(20,15))

----------------------

.085164835164835

(1 row(s) affected)

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

I'm not sure if I understand you correctly, but CASTing to DECIMAL works for me

declare @a float

set @a =8.5164835164835168E-2

select

@a

, round(cast(@a as decimal(5,3)),3)

, cast(round(@a,3) as decimal(5,3))

----------------------------------------------------- ------- -------

8.5164835164835168E-2 .085 .085

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RonyA SSC Veteran Points: 270 More actions · Answer 3

Hi,

Thanks for the replies.

I'm not sure why, but when typing:

select cast(8.5164835164835168E-2 as decimal(5,3))

I also get 0.85, which is exacly what I want.

But when using it in my query, I get 8.5000000000000006E-2.

In the query, the input is not a pure number. It's a calculation (multiplaction of 2 float, and division by an integer):

cast(

(31.0-DAY(t1.U_StrtDate)+1.0)*

(CAST(t0.U_PlnDays AS float)*9.0+CAST(t0.U_PlnHours AS float))/

DATEDIFF(dd,t1.U_StrtDate,t1.U_FnshDate)

as deciaml (5,3))

Does anyone have an idea why?

Thanks again,

Rony

Aaron West SSC Eights! Points: 808 More actions · Answer 4

Readers of this cannot debug your expression. Perhaps you could determine all the input values, eg:

select cast( (31.0-20+1.0)*

(CAST(3 AS float)*9.0+CAST(5 AS float))/

30

as decimal (5,3))

-------

12.800

(1 row(s) affected)

However, as you can see, in SQL Query Analyzer, this value displays in decimal form, not exponential. If the value is being retrieved by a client application, it may then be converted to a floating point value. If the client is, for example, a Perl script, DBI might automatically determine that floating point is the most efficient way to retrieve the data. In that case, as with some other client application languages, I would recommend you CAST to VARCHAR before returning to the client.

select CAST(CAST( 8.3333e-2 AS decimal (5,3)) AS VARCHAR(6))

UNION ALL

select CAST(CAST( 1.333e-1 AS decimal (5,3)) AS VARCHAR(6))

UNION ALL

select CAST(CAST( 1e/7e AS decimal (5,3)) AS VARCHAR(6))

UNION ALL

select CAST(CAST( 100e/7e AS decimal (5,3)) AS VARCHAR(6))

------

0.083

0.133

0.143

14.286

(4 row(s) affected)

Note that 1e = 1e0, which is a FLOAT. Each of the following (except #2 and #3) has a different internal representation:

SELECT CAST(CAST(1e AS REAL)AS VARBINARY) UNION ALL --REAL

SELECT CAST(CAST(1e AS FLOAT)AS VARBINARY)UNION ALL --FLOAT

SELECT CAST(1e AS VARBINARY) UNION ALL --FLOAT

SELECT CAST(1.0 AS VARBINARY)UNION ALL --Decimal(2,1) (?)

SELECT CAST(1. AS VARBINARY) UNION ALL --Decimal(1,0) (?)

SELECT CAST(1 AS VARBINARY) --INT

--------------------------------------------------------------

0x3F800000

0x3FF0000000000000

0x020100010A000000

0x0100000101000000

0x00000001

(6 row(s) affected)

But client applications have no knowledge of the DECIMAL datatype. So the DECIMAL gets converted to either a string or a double-precision floating point number before being sent back to the client application.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

To me it looks like you're complicating things here

What's the purpose of this whole exercise? What should this calculation mean?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

srikanth B Valued Member Points: 73 More actions · Answer 6

I am also facing the same problem

I used varchar to store the value

Declare @val Varchar

set @val = '-3.16E-05'

Select cast(@val as decimal(38,10))

gives me a error ..

Server: Msg 8115, Level 16, State 6, Line 4

Arithmetic overflow error converting varchar to data type numeric.

can any one help me

noeld SSC Guru Points: 96590 More actions · Answer 7

srikanth B :

Not all numbers are representable in finite digits arithmetics you may want to try:

Declare @val Varchar

set @val = '-3.16E-05'

Select cast(cast(@val as float) as decimal(38,10))

hth

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 8

--srikant B and Noel.

--I have noticed problems in VARCHAR itself.

Declare @val Varchar

set @val = '-3.16E-05'

SELECT @val Value --gives a value of.

Value

-----

-

--The value in VARCHAR field itself not a valid value.

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 9

govinn That is just a display problem;

simply change Declare @val Varchar to Declare @val Varchar (20)

and be happy

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 10

Roy Rogers

SSCertifiable

Points: 7712

May 31, 2005 at 12:21 pm

#561984

My mind was sleeping. Thanks

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 11

That's happened to me a lot ... so nothing to worry about. I usually get more coffee

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 12

I cannot produce these results. Maybe my settings are goofy.

DECLARE @val varchar(200)

SET @val = '-3.16E-05'

SELECT CAST( CAST( @val AS float) AS decimal(38,37))

SELECT CONVERT( decimal(38,37), CONVERT( float, @val))

SELECT CONVERT( float, @val)

----------------------------------------

0.00

----------------------------------------

0.00

-----------------------------------------------------

-3.1600000000000002E-5

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 13

decimal(38,37) the presicion you are setting is way too far for the number representation. You are telling SQL to use 37 out of 38 digits to represent values after the decimal point. the Decimal point is what is left and no room is left for the integer part.

try changing it to a more reasonable setting

eg dec(38,12) or dec(38,10)

hth

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 14

I should have specified that I already went through all iterations, (e.g. 38,5 - 38,10 - 38,15 - etc.) of those settings. I just posted my last attempt, not even thinking that I knew decimal(38,37) was preposterous.

I wasn't born stupid - I had to study.