data type conversion

[rds80]

NS.Numerator is of type money,
I'm using that field in a select statement, but the error: Error converting data type varchar to numeric. comes up. So I tried this CONVERT(money, CONVERT(varchar, NS.Numerator)), but still get the error.

Please help. Thanks.

 

[johnv20]

try

select cast(NS.Numerator as varchar)

 

[SQLBill]

Can you show us your select statement?

-SQLBill

Posting advice: FAQ481-4875

 

[rds80]

ACC is of type money. The value in CM.ACC is .0000.

I expect

CASE WHEN CM.ACC IS NULL OR CM.ACC  = 0 THEN ''
ELSE CONVERT(varchar, (LA.Numerator/CM.ACC)) * 100 END

to show a blank cell. However, the cell has the value .0000

Is this because sql interprets 0 differently then .0000?

Thanks.

 

[gmmastros]

CM.ACC is the denominator. Checking for 0 is good to prevent divide by zero errors. However, if LA.Numerator is 0 (and knowing that 0 divided by any number is always 0), you will still get a 0.

So... Do you have any 0's in the LA table?

Select * From LA Where Numerator = 0



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[rds80]

No 0s.
My co-worker helped me with the problem. It should have been:

CASE WHEN CM.ACC IS NULL OR CM.ACC  = 0 THEN ''
ELSE CONVERT(varchar, (LA.Numerator/CM.ACC) * 100) END

Parantheses were in the wrong place.

 

[SQLBill]

Yes, and what was happening was you were converting and then trying to multiply the VARCHAR result by 100. That didn't work. Once you fixed the parenthesis, you did all the math, then converted the results to VARCHAR.

That's why it's good to supply the code.

-SQLBill

Posting advice: FAQ481-4875