CODEWITHSUNDEEP
sqltype-conversionssms
modernoddity
modernoddity

Reputation: 13

Char to Decimal

I am trying to multiply two fields together. Problem is, one is CHAR and one is DECIMAL. 

OEAUDD.QTYSHIPPED = DECIMAL
LITERS.[VALUE] = CHAR

(This column holds more than just numbered values so I'm not able to permanently change the data type in that column. However, all of the data I'm pulling is numerical only and I need to be able to multiply it by qtyshipped)

Original code: 

CONVERT (DOUBLE PRECISION, TABLENAME.QTYSHIPPED*LITERS.[VALUE]) AS 'TotalLitersSold'

This was working fine for a while and then suddenly started throwing error:

Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric

Attempted: 

CONVERT (DECIMAL,LITERS.[VALUE])

and got the same error.

Attempted: 

CAST (LITERS.[VALUE] AS DECIMAL)

and got the same error.

Is there any way to change the data type of this column to decimal so I can multiply the two values together?

Thank you in advance for the assistance.

Upvotes: 0

Views: 344

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use TRY_CONVERT() and use it on each value separately:

(TRY_CONVERT(DOUBLE PRECISION, TABLENAME.QTYSHIPPED) *
 TRY_CONVERT(DOUBLE PRECISION, LITERS.[VALUE])
)

Upvotes: 1

Related Questions