Sql-server – Casting fraction number to decimal throws Arithmetic overflow error

castsql servert-sql

I have a table called AssignmentMarks that stores Student's assignment marks for different subjects.
This table has a column called Marks varchar(10) which stores the marks. The marks can be like 1, 2, and also it can be +, - or -+ which represents each sign a specific marks.

Note: + is 1 mark, - is -1 and -+ is 0.5

While getting a student marks from the table I am facing the Arithmetic Overflow error which I don't know what is the cause of this error.

The query is as follow:

SELECT SUM(
   CAST(
      CASE 
       WHEN a.Marks IS NULL OR a.Marks = '' THEN 0
       WHEN a.Marks = '+' THEN 1
       WHEN a.Marks = '-' THEN -1
       WHEN a.Marks = '-+' THEN 0.5
       ELSE a.Marks
      END AS DECIMAL(5,2)
   )
)
FROM AssignmentMarks AS a WHERE a.StudentID=10
AND a.SubjectID=1

After Executing the above query I get the following error.

Msg 8115, Level 16, State 8, Line 7

Arithmetic overflow error converting varchar to data type numeric.

Any idea what is the main cause of this error?

Sample Data: SQL Fiddle

Best Answer

From error message,

Arithmetic overflow error converting varchar to data type numeric.

My first place where I could change is :

 ELSE a.Marks

and change it to

ELSE try_convert(int, a.Marks)

or convert to decimal , if you want

Later edit:
In case expression you have a few THEN with different data types: int, numeric, varchar. Due to data type precedence, when combines different data types, the lower data type is converted to the type with higher precedence See here information about it.

you can see this , by this example:

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0 as col) as a  

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 1 as col) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select -1 as col ) as a

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select 0.5 as col) as a   

SELECT   SQL_VARIANT_PROPERTY(col,'BaseType') AS 'Base Type',  
         SQL_VARIANT_PROPERTY(col,'Precision') AS 'Precision',  
         SQL_VARIANT_PROPERTY(col,'Scale') AS 'Scale'
FROM
  (select '-+' as col) as a 



Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10          0
Base Type   Precision   Scale
int         10           0
Base Type   Precision   Scale
numeric     1           1
Base Type   Precision   Scale
varchar     0           0

Related Solutions

Sql-server – Arithmetic overflow error converting float to data type numeric

I'm no expert in this, but it looks to me as if you're trying to use FLOAT. Have you tried using NUMERIC? Something like this maybe? Just throwing ideas out.

88  SQLNUMERIC 0   0   "," 89  PPL_2_BL    ""

Sql-server – Change language used to parse decimal number

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost varchar(32);
ALTER TABLE dbo.Payment ADD dcost 
  AS (CONVERT(decimal(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num varchar(32) = '3,6';
SELECT CONVERT(decimal(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.

Best Answer

Related Solutions

Sql-server – Arithmetic overflow error converting float to data type numeric

Sql-server – Change language used to parse decimal number

Related Question