Arithmetic overflow error converting float to data type numeric

Question

Post reply

Arithmetic overflow error converting float to data type numeric

Viewing 8 posts - 16 through 22 (of 22 total)

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

apat SSCertifiable Points: 5929 More actions · Answer 1

Yes CAST is not needed for 10000.0..

probably case statement to differentiage positive and negative values from column1.

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

As an alternative to a CASE expression, you can use the SIGN function:

select column1, SIGN(column1) * POWER (abs(column1), 1.8) * CAST(10000 as float) from #t

apat SSCertifiable Points: 5929 More actions · Answer 3

Yes, this works... Thanks a lot everyone...

One more point,

I also would like to do the opposite for power function as well...

Instead of ---- POWER (abs(column1), 1.8)

Now I want --- POWER (1.8, column1)

and column1 has values ranging from -970.81 to 970.81...

HowardW One Orange Chip Points: 29109 More actions · Answer 4

Well, mathematically speaking you can have any positive/negative number as an exponent but I'm really intrigued as to what the business logic is for needing this.

Care to elaborate?? 🙂

apat SSCertifiable Points: 5929 More actions · Answer 5

To be honest, all I know is this is the function to be applied on the data... don't know the real business logic behind it...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

You need to cast 1.8 as a float. See the following:

create table #temp (

column1 float

);

declare @TestVar float;

set @TestVar = 975.81;

insert into #temp select @TestVar;

select column1

, POWER (column1, 1.8) * 10000.0

, POWER (cast(1.8 as float), column1) * 10000.0

from

#temp;

apat SSCertifiable Points: 5929 More actions · Answer 7

apat

SSCertifiable

Points: 5929

March 5, 2010 at 9:57 am

#1129283

works... you guys rock...

Thanks a lot....

LutzM SSC Guru Points: 107049 More actions · Answer 8

apat (3/5/2010)
Yes, this works... Thanks a lot everyone...
One more point,
I also would like to do the opposite for power function as well...
Instead of ---- POWER (abs(column1), 1.8)
Now I want --- POWER (1.8, column1)
and column1 has values ranging from -970.81 to 970.81...

I strongly recommend that you reread the whole thread in order to reevaluate your repeated request.

It's been stated more than once that the calculation you're trying to make cannot be done with SQL Server due to the sheer amount of digits.

To repeat a similar sample given earlier by Lynn:

SELECT POWER(1.8,140) =

547205828359370920000000000000000000.0

If I'm guessing correctly you're looking for a number with som 250 digits...

But there are some good news as well:

I'm able to calculate POWER (1.8, -970.81 ) !

The result is 0.000000000000000000000000000000 (there will be a number different than zero after approx. 250 digits). Accurate enough to round it to Zero?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]