SQL Server Performance Forum – Threads Archive

Converting INT

Hello Everyone, I have one queries related to INT & BIGINT data type.I have below Table. create table Testing
( Qty int,
Price int) —INSERTING DATA Insert into Testing Values(1234567890,987654321) /****I have below query****/ select Qty*Price from Testing /*** Above SELECT fail with error as "Arithmetic overflow error converting expression to data type int."***/ Select Convert(bigint,Qty*Price) from Testing /*** Above SELECT again fail with error as "Arithmetic overflow error converting expression to data type int."***/ Select Convert(bigint,Qty)* Convert(bigint,Price) From Testing /*** Above SELECT works fine**/ My question is why second SELECT fail. I am converting output of int into bigint and bigint can take values upto 19 digit then Why SQL is displaying Arithmetic overflow error. Please explain for more clarity Thanks and Regards
Ravi K

---

Convert both to bigint and multiply Select Convert(bigint,Qty)*Convert(bigint,Price) from Testing
Madhivanan Failing to plan is Planning to fail

---

Hi Madhivanan, Thanks for your answer but my question is why my below queries give "Arithmetic overflow" error. Select Convert(bigint,Qty*Price) from Testing Thanks and Regards
Ravi K

---

Becuase The result Qty*Price exceeds maximum int value. Multiplication will be done first then Convert. So you get this error Other easy is to multiply it by 1.0 Select 1.0*Qty*Price from Testing
Madhivanan Failing to plan is Planning to fail

---

]]>