none
Error : Arithmetic overflow error converting numeric to data type varchar. RRS feed

  • Question

  • getting error in following script but now able to find out where please help.



    DECLARE @CurrentDate DATETIME --= Fireball_Configuration.dbo.PreviousBusinessDay()

    SELECT
    @CurrentDate = MAX(DATE)
    FROM
    Fireball_Reporting..Reporting_LoanPrices

    DECLARE @CompareDate DATETIME = Fireball_Configuration.dbo.PreviousBusinessDayByDate(@CurrentDate)
    DECLARE @count INT = 5

    DECLARE @EmailHeader VARCHAR(500) = Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'EmailSettings - Header')
    DECLARE @EmailFooter VARCHAR(500)= Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'EmailSettings - Footer')
    DECLARE @tableHTML VARCHAR(MAX)



    SET @tableHTML = @tableHTML +
    '<H1>Top '+ CONVERT(VARCHAR(20),@COUNT) +' Distressed Losers</H1>' +
    '<TABLE border = "1" cellspacing="0" cellpadding="5">' +
    '<TR><TH>Company</TH><TH>Security</TH><TH>Current Date</TH><TH>Current Price</TH><TH>Compare Date</TH><TH>Compare Price</TH><TH>Delta</TH></TR>' +
    CAST
    (
    (
    SELECT

    CONVERT(VARCHAR(8),CONVERT(DECIMAL(8,4),((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) + '%' AS Delta
    FROM
    (
    Fireball_Reporting..Reporting_LoanPrices LoanPrices
    INNER JOIN
    Fireball..Security ON
    Security.SecurityId = LoanPrices.SecurityId
    INNER JOIN
    Fireball..Company ON
    Company.CompanyId = Security.CompanyId
    ) CurrentLoans+
    '</TABLE>' + @EmailFooter;

    Ashish Fugat (9960978134) Software Engineer
    • Edited by ashuthinks32 Thursday, December 22, 2011 5:30 AM
    Wednesday, December 21, 2011 3:45 PM

Answers

  • Verify that the value returned from calculation ((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) is not actually crossing 9999 as you are converting it to DECIMAL(8,4) only 4 digits are allowed before decimal.

    If you expect the value from calculation could be more, increase the precision value in DECIMAL from 8 to 10 or 12 and adjust the VARCHAR(8) to accommodate that many characters.

    Hope this helps.

    Thanks!

    • Proposed as answer by Naomi NModerator Thursday, December 22, 2011 2:29 AM
    • Marked as answer by ashuthinks32 Thursday, December 22, 2011 5:30 AM
    Wednesday, December 21, 2011 3:56 PM

All replies

  • Verify that the value returned from calculation ((CurrentLoans.Price - PreviousLoans.Price) / PreviousLoans.Price) * 100)) is not actually crossing 9999 as you are converting it to DECIMAL(8,4) only 4 digits are allowed before decimal.

    If you expect the value from calculation could be more, increase the precision value in DECIMAL from 8 to 10 or 12 and adjust the VARCHAR(8) to accommodate that many characters.

    Hope this helps.

    Thanks!

    • Proposed as answer by Naomi NModerator Thursday, December 22, 2011 2:29 AM
    • Marked as answer by ashuthinks32 Thursday, December 22, 2011 5:30 AM
    Wednesday, December 21, 2011 3:56 PM
  • Facing the same issue

    I have the below table defined as

    DECLARE @Table Table(tblData Numeric(28,15))
    Insert into @Table (tblData) values(159.00000000000000)

    I have tried inserting various values..it accepts data till 129,  it throws this error at 130 and above.

    Getting the below Error:Arithmetic overflow error converting numeric to data type numeric.

    Its urgent ples help.


     
    Wednesday, June 13, 2012 8:32 AM
  • I ran the script you posted successfully.

    And I find it difficult to believe that exact script you posted would ever give that error.

    But if you have a real table with a trigger, maybe something funny is happening in the trigger.

    Also, when you have a problem, start a new thread rather than posting to an old thread.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 13, 2012 10:29 AM