CODEWITHSUNDEEP
sqlsql-server
snapper
snapper

Reputation: 210

SQL: Convert type varchar to type money to be divided. **Divide by zero error encountered**

Error received:

Divide by zero error encountered

I need to convert type varchar in SQL Server to be able to divide by type money. I have searched the forums a bunch and can't seem to find an answer that works. Below is what I have currently. When all is done I need to display the answer as a percentage in a report. Thanks to anyone who can help! 

  cast((CONVERT(DECIMAL, ((OEJUD.Text7/OEJUD.Money1) * 100)))AS varchar(20)) + '%' AS '% Prog Billed'

Upvotes: 0

Views: 349

Answers (4)

HLGEM
HLGEM

Reputation: 96600

While the NullIf code works, you may need to return something other than null depending on your business requirements, in that case you need a case statement

case when OEJUD.Money1 is null or OEJUD.Money1= 0 or isnumeric(EJUD.Text7) = 0 
then '0'
else   
cast(
            (cast(OEJUD.Text7 as money)/EJUD.Money1) * 100

    AS varchar(20)) + ' %' 
End AS '% Prog Billed'

Upvotes: 1

Jaimal Singh
Jaimal Singh

Reputation: 1

DECLARE @myMoney Money
DECLARE @myAmount varChar(30)

SET @myMoney = 3
SET @myAmount = '10'

Select cast((CONVERT(DECIMAL, ((@myAmount/NULLIF(@myMoney,0)) * 100)))AS varchar(20))

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

This should work

select  CONVERT(VARCHAR(20),((cast(OEJUD.Text7 as float)/NULLIF(cast(OEJUD.Money1 as float),0))*100))

check this sql and after that try

CONVERT(VARCHAR(20),((cast(OEJUD.Text7 as float)/NULLIF(cast(OEJUD.Money1 as float),0))*100)) + '%' AS '% Prog Billed'

Upvotes: 1

Fabiano Carvalho
Fabiano Carvalho

Reputation: 512

Your type.varchar column is set to 0, some value divided by 0 does not exist.

Upvotes: 0

Related Questions