multiplication in ssms gives error

select 5494516 * 8192 gives Arithmetic overflow error converting expression to data type int. why- is there limit?
LVL 5
25112Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
LIONKINGConnect With a Mentor Commented:
Becuase since you don't specify the datatype of either value, SQL Server assumes they're both integers, therefore the result is also an integer. Since the value exceeds the maximum value for an int datatype, it throws an overflow exception.

What you can do is to cast one of the values (or both) to a datatype that can hold a bigger value (i.e. float).

I tried this and it worked.

select cast(5494516 as float) * cast(8192 as float)
0
 
rajvjaConnect With a Mentor Commented:
Hi

select 5494516 * 8192.0

this is sufficient
0
 
Anthony PerkinsConnect With a Mentor Commented:
I would not use float, as after all it is an aproximate data type. Instead use bigint, as in (fix the obvious typo in xSELECT and xCAST):
xSELECT  xCAST(5494516 AS bigint) * 8192
0
 
25112Author Commented:
perfect..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.