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.
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.
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.
