none
Arithmetic overflow error RRS feed

  • Question

  • Hi, I am getting the following error message. How I can run the query without any error.
    Select * from CR_Report where percentage=0.00

    --Error
    Arithmetic overflow error converting varchar to data type numeric.

    Thanks.

    Tuesday, July 26, 2011 6:51 PM

Answers

  • Hi Sulatana

     

    You can use the below code 

     

    Select * from CR_Report where CONVERT(numeric(4,2),percentage)>0
    

    let us know the results

     


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by Khan_K Tuesday, July 26, 2011 7:39 PM
    Tuesday, July 26, 2011 7:20 PM

All replies

  • What is CR_Report - is it a view or a table?

    What type is Percentage column?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, July 26, 2011 6:52 PM
    Moderator
  • If column "percentage" is not a decimal or integer (but for example a varchar), then this might happen if not all values can convert to a decimal.

     


    Gert-Jan
    Tuesday, July 26, 2011 7:01 PM
  • CR_Report is a table.
    Tuesday, July 26, 2011 7:02 PM
  • Hi Sultan 

     

    can you please let us know the table definition of CR_report, the out put of the below command

     

    sp_help CR_Report
    


    and also the the output of the below command

    select top 5* from CR_report
    

    both of these outputs will let us understand what is happening with your query, or else we will searching for a black cat in the darkest night....


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, July 26, 2011 7:05 PM
  • try :

    Select * from CR_Report where isnumeric(percentage)=1 and percentage=0.00 
    

     


    Best regards
    Tuesday, July 26, 2011 7:11 PM
  • I think  that  Percentage column is type varchar or nvarchar, try with code:

    Select * from CR_Report where CAST(percentage AS decimal)=0.00

    info@geohernandez.com - geeks.ms/blogs/ghernandez

    Tuesday, July 26, 2011 7:12 PM
  • CR_Report is a table.percentage is a varchar and need to convert into numeric and need to run the following query
    Select * from CR_Report where percentage>0

    --Error message
    Arithmetic overflow error converting varchar to data type numeric.

    Tuesday, July 26, 2011 7:13 PM
  • CR_Report is a table.percentage is a varchar and need to convert into numeric and need to run the following query
    Select * from CR_Report where percentage>0

    --Error message
    Arithmetic overflow error converting varchar to data type numeric.


    try :

    Select * from CR_Report where isnumeric(percentage)=1 and percentage>0
    

     


    Best regards
    Tuesday, July 26, 2011 7:15 PM
  • Hi Sulatana

     

    You can use the below code 

     

    Select * from CR_Report where CONVERT(numeric(4,2),percentage)>0
    

    let us know the results

     


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by Khan_K Tuesday, July 26, 2011 7:39 PM
    Tuesday, July 26, 2011 7:20 PM
  • Other possibility, If % is a part your pourcentage :

    declare @TAb table (percentage varchar(50))
    insert @tab
    select '0%' union all
    select '10%' union all
    select '-15%' union all
    select '100%' 
    select * from @TAB where cast(replace(percentage,'%','') as numeric)>0
    --10%
    --100%
    

     

     


    Best regards
    Tuesday, July 26, 2011 7:26 PM
  • CR_Report is a table.percentage is a varchar and need to convert into numeric and need to run the following query
    Select * from CR_Report where percentage>0

    --Error message
    Arithmetic overflow error converting varchar to data type numeric.

    If you have a varchar colunm and want to compare it with a numerical value, you are in for trouble, because there is a risk that there are valuess in that column that cannot be converted. A common problem is that there is are text in the column, but in this case, there is apparently a value that is too big to fit.

    Well, one problem is that the data type of 0.00 is decimal(3, 2), so if there is a value such as 10.23 in the column you get this overflow. But there can be values of any size in that column. Overall, it is a bad idea to store data you want to use numerically in a text column for various reasons.

    You could try:

    Select * from CR_Report where convert(decimal(18,2), percentage) = 0

    But there is no guarantee that it would work. This is better:

    SELECT * FROM CR_Report
    WHERE CASE WHEN isnumeric(percentage) = 1
                    THEN convert(decimal(18,2), percentage)
          END = 0

    But it is not waterproof.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 26, 2011 10:14 PM