Overflow error when inserting decimal into SQL table

Question

Getting the error Arithmetic overflow error converting numeric to data type numeric. in MS SQL. Here's the table:

CREATE TABLE [Percentages]
(
    [enabled] bit,
    [name] nvarchar(150),
    [percent] decimal(5,5)
)

And here's what I'm trying to insert:

INSERT INTO [Percentages] ([enabled], [name], [percent])
VALUES (1,'Test',2.0)

If the decimal limit is 00000.00000 to 99999.99999 then why is 2.0 giving me the error? "2" gives the same error as well

(5,5) means 5 digits in total, and 5 of those as decimals. (I.e. from -0.99999 to 0.99999.) You want decimal(10,5). — jarlh
– jarlh, Commented Mar 12, 2019 at 13:37

jarlh · Accepted Answer · 2019-03-12 13:39:45Z

3

decimal(5,5) means 5 digits in total, and 5 of those are decimals. (I.e. from -0.99999 to 0.99999.)

You want decimal(10,5)!

answered Mar 12, 2019 at 13:39

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Comments

Nandhini · Accepted Answer · 2019-03-12 13:53:06Z

0

Decimal(5,5) depicts - Decimal(p,s) in which s is the scale, number of digits right to the decimal point. p is the precision , total number of digits to be stored in decimal. Subtracting s from p gives the number of digits to the left of decimal point.

In your case - Decimal(5,5) allows 0 digits to the left of decimal point. Hence it throws an error.

Try giving : decimal(10,5) or other value based on your maximum expected value.

answered Mar 12, 2019 at 13:53

Nandhini

6657 silver badges21 bronze badges

Collectives™ on Stack Overflow

Overflow error when inserting decimal into SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related