Resolving "Arithmetic overflow error converting varchar to data type numeric" in SQL for insert statement

Question

I want to create a SQL table with the following column datatypes.

Name : Varchar
Grade : Data will have “#” followed by numbers and also can be varchar. Eg : #1, #2, TML
Sizes : Can be whole numbers and fractions. Eg: 26/30, 80, 85/69
Average : Will be decimal numbers.

I have created table based on the above requirements:

CREATE TABLE [dbo].[Report_Proj](
[Name] [nvarchar](255) NOT NULL,
[Grade] [nvarchar](50) NOT NULL,
[Sizes] [float](10) NOT NULL,   
[Average][decimal](10, 10) NOT NULL

But when I insert data into this table I’m getting the error “Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting varchar to data type numeric. The statement has been terminated.”

Where could I possibly be going wrong. Need the above data for reporting purposes and will not have any arithematic calculations in future.

mohan111 · Accepted Answer · 2015-03-18 08:42:24Z

2

just change the decimal value data type to (10,2)

declare  @Report_Proj TABLE (
    [Name] [nvarchar](255) NOT NULL,
    [Grade] [nvarchar](50) NOT NULL,
    [Sizes] [float](10) NOT NULL,   
    [Average][decimal](18, 2) NOT NULL)

    insert into @Report_Proj ([Name],[Grade],[Sizes],[Average])values ('ram','#1',26/30,10.2)

    select * from @Report_Proj

answered Mar 18, 2015 at 8:42

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

aruna Over a year ago

Thank U! It's resolved. Have been struggling with it for some time!

aruna Over a year ago

Hi, I have a small issue here, I have observed that the "Sizes" value is "0" and not 26/30 after I run the statement. Is it possible to display 26/30 in the column?

mohan111 Over a year ago

even if you give float because of '/' divided by symbol it is getting divided..instead of that change that data type to varchar(10) and give value in '26/30' in single quotes

aruna Over a year ago

It has executed now. Thnx a ton!

aruna Over a year ago

How I wish I could. I have not got enough reputation to upvote your answer :( . Will do once I can.

Collectives™ on Stack Overflow

Resolving "Arithmetic overflow error converting varchar to data type numeric" in SQL for insert statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related