CODEWITHSUNDEEP
sqlsql-servert-sql
Lynchie
Lynchie

Reputation: 1149

Divide By Zero - Can't see why this would fail

I have written a function just to work out the Pro-Rata Clawback on any amount me typing it out constantly.

However I get a Divide By Zero Error everytime it runs, I broke the function down very simply.

DECLARE @ProRataAmount DECIMAL(8,2), @DaysRemaining INT,@NetValue DECIMAL(8,2), @TimeOnRisk INTEGER

SET @NetValue = 29.00
SET @TimeOnRisk = 8

SET @DaysRemaining = (365 - @TimeOnRisk)

PRINT @DaysRemaining
PRINT @NetValue

SELECT @NetValue / (@DaysRemaining/365)

There is NO value there that is dividing by zero, so why is it returning said error?

Cheers.

Upvotes: 2

Views: 53

Answers (2)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

Dividing an integer with an integer will cause the problem here:

SELECT @NetValue / (@DaysRemaining/365)

Here you will always divide by zero because @DaysRemaining is less than the divisor. You can change the calculation around to get past this easily while keeping the value as integer:

SELECT @NetValue * 365 / @DaysRemaining

Upvotes: -1

John Cappelletti
John Cappelletti

Reputation: 81970

An INT/INT will return an INT.

Try the following (notice the 365.0):

SELECT @NetValue / (@DaysRemaining/365.0)

Now, just in case @DaysRemaining is ZERO, you can still generate a Divide By Zero unless you trap it like so:

SELECT @NetValue / (NullIf(@DaysRemaining,0)/365.0)

Upvotes: 5

Related Questions