CODEWITHSUNDEEP
sqlsql-server
Jan Trindal
Jan Trindal

Reputation: 219

Overflow error when inserting decimal into SQL table

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

Upvotes: 4

Views: 1540

Answers (2)

Nandhini
Nandhini

Reputation: 665

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.

Upvotes: 0

jarlh
jarlh

Reputation: 44766

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)!

Upvotes: 3

Related Questions