Arithmetic overflow error converting numeric to data type numeric on insert

Question

I am having trouble with this query that shows Arithmetic overflow error.

INSERT INTO seed_health_testing_form (seedlotno, modeoftesting, datetested, 
    datecollected, placecollected, sampleno, germination1, germination2, 
    germination3, germination4, germination5, germination6, germination7, 
    germination8, germination9, germination10, remarks) 
VALUES ( 'A00075', 'GTest', '2/14/2013', 
    '2/19/2013', 'Laguna', 0, 50.00, 50.00, 
    50.00, 50.00, 0.00, 0.00, 0.00, 
    0.00, 0.00, 0.00, '')

Table Design

[id]                [int]       IDENTITY(1,1) PRIMARY KEY NOT NULL
[seedlotno]         [varchar](50)   NULL
[modeoftesting]     [varchar](100)  NULL
[datetested]        [datetime]      NULL
[datecollected]     [datetime]      NULL
[placecollected]    [varchar](100)  NULL
[sampleno]          [int]           NULL
[germination1]      [decimal](3, 2) NULL
[germination2]      [decimal](3, 2) NULL
[germination3]      [decimal](3, 2) NULL
[germination4]      [decimal](3, 2) NULL
[germination5]      [decimal](3, 2) NULL
[germination6]      [decimal](3, 2) NULL
[germination7]      [decimal](3, 2) NULL
[germination8]      [decimal](3, 2) NULL
[germination9]      [decimal](3, 2) NULL
[germination10]     [decimal](3, 2) NULL
[remarks]           [varchar](1000) NULL

I tried breaking the INSERT query line by line to trace where the error was but it always point to Line 1. Still I can't find where my problem is.

LittleBobbyTables - Au Revoir · Accepted Answer · 2013-02-26 17:53:19Z

7

All of your [decimal](3, 2) data types are the issue.

The (3,2) tells you that the number can have a precision of 3 digits total, with 2 digits being reserved for the decimal portion.

Thus, you only have 1 digit available to store values like 50, which causes the overflow.

See the decmial datatype documentation for more information.

answered Feb 26, 2013 at 17:53

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

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

1 Comment

pvzkch Over a year ago

I thought (3,2) was 3 whole number and 2 decimal digits. That is why 1.00 doesn't give me error. Thanks

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type numeric on insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related