Trying to get rid of “divide by zero” error message using SQL Server 2008

55,945 pts.
Tags:
SQL Server 2008
SQL Server error messages
In SQL Server 2008, I keep receiving this error message:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Now, this is more of a general question but what is the best way to write SQL code so this message will never appear again.
ASKED: September 20, 2013  1:45 PM
UPDATED: October 31, 2013  2:16 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    There could be several ways to avoid it depending on the specific context, but in general, if there is at least a remote possibility of the divisor being zero, you could use a CASE construct to return something specific when it is zero, or use the NULLIF function on the divisor to return null when it is zero (thus making the final result null as well).
    65,110 pointsBadges:
    report
  • juelcse
    This is my syntax:

    SELECT Ap.ID AS SL,
                AP.AssetPurchaseDate AS Purchase_Date
                ,AP.AssetID AS Asset_ID_Number,
                C.CategoryName, SC.SubCategoryName,
                AP.AssetName AS Name_of_Asset ,--AP.AssetPurchaseValue AS Purchase_value,
                AP.AssetQuantity AS Qnt,
                AP.AssetPresentValue AS Present_Value,C.DepreciationRate+'%' AS Rate,
                
       -- Using MONTH function to find month from AssetPurchaseDate
                MONTH(AP.AssetPurchaseDate) AS Number_of_Month,
                
       -- Using YEAR function to find current year from AssetPurchaseDate
                YEAR(GETDATE()) AS Year,
                
                
       -- Calculation Of Individual  Asset Depreciation( By using AssetPresentvalue AND Depreciation Rate)
                (AP.AssetPresentValue*C.DepreciationRate)/100 AS Charge_Current_Year, --Finding One Year depreciation
                ((AP.AssetPresentValue*C.DepreciationRate)/100)/12 AS Charge_Of_MonthRate, -- Finding One month Depreciation
                (((AP.AssetPresentValue*C.DepreciationRate)/100)/(12-MONTH(AP.AssetPurchaseDate))) AS Charge_Of_Amount, --Finding Till This month Depreciation
     
      -- Calculation  Writen Down Value(WDV) Of Individual  Asset
                AP.AssetPresentValue-(AP.AssetPresentValue*C.DepreciationRate)/100 AS WDV_Current_Year, ---Finding WDV for One Year
                (AP.AssetPurchaseValue-((AP.AssetPresentValue*C.DepreciationRate)/100)/12) AS WDV_For_One_Month, -- Finding WDV for One Month
                AP.AssetPresentValue-(((AP.AssetPresentValue*C.DepreciationRate)/100)/(12-MONTH(AP.AssetPurchaseDate))) AS WDV_As_On --- Finding WDV for Till_This_Month
            
            FROM Assets AP
            
            INNER JOIN AssetCategory C ON C.ID=AP.CategoryID
            INNER JOIN AssetSubCategory SC ON SC.ID=AP.SubCategoryID
            WHERE AP.AssetStatus='A'
            ORDER BY CategoryName ASC

    Give this error massage:

    Msg 8134, Level 16, State 1, Line 1
    Divide by zero error encountered.

     
    10 pointsBadges:
    report
  • carlosdl
    If 
    MONTH(AP.AssetPurchaseDate)
    gives 12, then you will have a division by zero.  This possibility needs to be taken into account.
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following