CODEWITHSUNDEEP
sqlsql-server
GabrielVa
GabrielVa

Reputation: 2388

SQL Convert statement

I need help with a SQL convert statement. I have NetQuanity (masterTable) which is a varchar(15) and I have another table with Purchase price (PO TABLE) which is money. When I try to multiply them in a SQL view is gives me the error:

enter image description here

Upvotes: 0

Views: 1078

Answers (3)

Dave
Dave

Reputation: 1234

First of all you have a data definition problem. The first thing is to eliminate any non-numeric entries in the master table. 

SELECT whatever FROM masterTable WHERE ISNUMERIC(NetQuanity)=1

The next step is to include this as a sub-query in the calculation.
In this query use CONVERT or CAST to convert the valid quanities to integer. i.e.

CONVERT(INT, NetQuantity)

Upvotes: 0

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143269

Maybe try using the CONVERT function? CONVERT(money,NetQuantity).

Upvotes: 2

George Johnston
George Johnston

Reputation: 32278

If your field is a VARCHAR, you'll need to CAST to the appropriate data type prior to your operation. e.g.

CAST(myVarCharField as INT) * myIntField

Be forewarned however, if you attempt to CAST this field to a numeric data type and it's not numeric, you'll be in the same boat.

I would recommend using CAST over CONVERT in your example, for the following reason defined in this SO post:

Related: T-SQL Cast versus Convert

Upvotes: 4

Related Questions