Arithmetic overflow error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Arithmetic overflow error

Author

Topic

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 15:44:12

select Convert(BIGINT,2768964354) & (Convert(BIGINT,0xFFFFFFFF) * Power(convert(bigint, 2),32))

when executing the above statement i am getting an error like

"Arithmetic overflow error converting expression to data type bigint"

if the power value is less than 32 it is coming correctly.

Thanks in advance
Kiran

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 15:54:31

Sounds like you're trying to convert from an int which has a max size of 2,147,483,647 (2^31 - 1)

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 15:58:22

Skorch ,

Thanks for your reply.

I am converting to bigint the value Power(convert(bigint, 2),32)) will not exceed the bigint but by multiplying with Convert(BIGINT,0xFFFFFFFF) will cross the limit.

how i can convert so that i can do a bitwise AND operation with a bigint value

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 16:16:49

Ah, found your answer. You're actually exceeding the maximum value for bigint which is 9,223,372,036,854,775,807.

You can test this like this:

select (cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)) * power(cast(2 as bigint), 31)
5,946,305,672,109,883,392

select (cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)) * power(cast(2 as bigint), 31.5)
8,409,346,124,811,212,646

select (cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)) * power(cast(2 as bigint), 31.6)
9,012,914,015,135,219,514

select (cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)) * power(cast(2 as bigint), 31.7)
Arithmetic overflow error converting expression to data type bigint.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-05 : 16:17:44

The highest positive value for BIGINT is
9,223,372,036,854,775,807.
power(2,31) brings already
9,223,372,034,707,292,160.
So there is no chance for (2,32).

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-05 : 16:18:45

Sorry - again - toooooooo late

No, you're never too old to Yak'n'Roll if you're too young to die.

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 16:20:27

ya i know that, i like to have that value i want to know what is the best way to cast that value so that i will get the desired results.
your kind help is greatly appriciated

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 16:23:30

Unfortunately SQL Server does not have a datatype larger than bigint. You may have to use another software for this calculation.

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 16:28:05

can i use something like Convert(BIGINT,2768964354) & Convert(varchar(20),(Convert(BIGINT,0xFFFFFFFF) * Power(convert(bigint, 2),32)))

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 16:40:57

After some more tinkering I think I may have something:

select cast(cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)as float) * power(cast(2 as bigint), 32)
1.18926113442198E+19

Also, my previous statement about bigint being the largest datatype in SQL Server pertains only to exact numerics. Float is an approximation.

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 16:50:38

we cannot do a boolean AND operation between float and bigint

the value exceeds the bigint when i multiply

Convert(BIGINT,2768964354) & Convert(varchar(20),(Convert(BIGINT,0xFFFFFFFF) * Power(convert(bigint, 2),32)))

that is why i converted to varbinary but i am not getting the desired result maybe something is missing.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 16:59:06

Did you try this?

select cast(cast(2768964354 as bigint) & cast(0xFFFFFFFF as bigint)as float) * power(cast(2 as bigint), 32)

It works for me and I get 1.18926113442198E+19 with it.

I'm doing the boolean AND between bigints, then casting the result of that as float and multiplying it later by a bigint.

kirandarisi
Starting Member

6 Posts

Posted - 2009-01-05 : 17:07:28

The & operation is for entire expression (Convert(BIGINT,0xFFFFFFFF) * Power(convert(bigint, 2),32))

you are performing only to cast(0xFFFFFFFF as bigint) which is not correct

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 17:23:54

I see what you mean now. I had the order of operations wrong there. I don't know of any way to accomplish what you're trying to. SQL Server won't let us compare floats with & and we can't cast the float to a precise datatype because it's out of range.

YP
Starting Member

1 Post

Posted - 2009-05-13 : 08:54:42

Hi

You should Try Power(2.0,32). This informs SQL that you are not dealing with integer values.

Cheers

Yashin

YP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources