Warning#1264:out of range error in mysql

Question

The following query:

INSERT INTO skill (`emp_number`, `skill_id`, `year_exp`, `comments`)
VALUES ('4', '3', '23.45', '')

It is producing the error :

1 row inserted.
Warning: #1264 Out of range value for column 'year_exp' at row 1
year_exp column is of datatype decimal(2,2)

Please help me to pint out the error.

Answer 1

Change fieldtype to decimal(4,2). Details: https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.7 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

Answer 2

I believe you're having this error because the year_exp field is DECIMAL(2,2), and you want DECIMAL(4,2). DECIMAL(2,2) means a number of precision 2, with up to 2 decimal places. But this number has 4 digits of precision.

This link to MSDN talks about the decimal precision.

http://msdn.microsoft.com/en-US/library/ms187746(v=SQL.90).aspx

Here's a quick test with similar results (done in SQL Server 2008, but I think you're on MySQL...)

1) Created a table with a test column:

CREATE TABLE testtable (testcolumn DECIMAL(2,2))

2) Ran insert statement... :

INSERT INTO testtable (testcolumn) VALUES (23.45)

... and received this error ...

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(COMMENT: one of my fave errors ... "cannot convert number to number ..." ha ha)

3) Altered column to proper specifications

ALTER TABLE testtable ALTER COLUMN testcolumn DECIMAL(4,2)

4) Re-ran same insert statement. It works.

Please let me know if this helps.