Convert when multiplying smallints

Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
ThanksTry,
SELECT cast(Quantity as bigint) * UnitCost FROM Transactions
AMB
"hals_left" wrote:

> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>|||Which of those statements produce the error? And do you know what values are
producing the error? Small int can go upto 32,767
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
Hi I have two columns of type small int that cause overflow when
multiplied.
SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
Arithmetic overflow error converting expression to data type smallint.
What is the correct way to select this?
Thanks|||SELECT Cast(Quantity as Bigint)*Cast(UnitCost as Bigint) FROM Transactions
works...but best option?
Lee-Z
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1123850544.468656.282800@.g14g2000cwa.googlegroups.com...
> Hi I have two columns of type small int that cause overflow when
> multiplied.
> SELECT Convert(Bigint,Quantity*UnitCost) FROM Transactions
> SELECT Convert(varchar(12),Quantity*UnitCost) FROM Transactions
> Arithmetic overflow error converting expression to data type smallint.
> What is the correct way to select this?
> Thanks
>