CODEWITHSUNDEEP
mysqldecimalsqldatatypes
Stephane
Stephane

Reputation: 5078

MySQL ERROR 1264 (22003): Out of range value for column

I get this error:

ERROR 1264 (22003): Out of range value for column 'median_comments' at row 1

after running this query:

update influencers set `median_comments` = 1347 WHERE `id` = 1;

I'm not sure why this fails on this number which doesn't have any decimals and which is only 4 digits.

The field type is:

median_comments   decimal(10,8)

Upvotes: 2

Views: 11985

Answers (3)

Serhii Popov
Serhii Popov

Reputation: 3804

As you like me came here from google and your issue is related to Doctrine, and your column type is type="decimal", then you should configure precision and scale properties of your column in the right way.

For me, it was like before:

/** @ORM\Column(name="revenue", type="decimal", scale=4, nullable=true) */
private $revenue;

after

/** @ORM\Column(name="revenue", type="decimal", precision=14, scale=4, nullable=true) */
private $revenue;

It will be converted to DECIMAL(14,4), which means fourteen digits total, four of which are to the right of the decimal point.

Don't forget to prepare migration and run it to apply the changes.

Finally, you should get SQL like this:

ALTER TABLE project_finance CHANGE revenue revenue NUMERIC(14, 4) DEFAULT NULL

Upvotes: 0

Dark Knight
Dark Knight

Reputation: 6531

You are using DECIMAL(10,8) that means max digits before decimal would be (10 - 8) = 2.

Reference: DECIMAL Data Type Characteristics

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

  1. M is the maximum number of digits (the precision). It has a range of 1 to 65.
  2. 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.

To fix the error, change your datatype to DECIMAL(10,2).

ALTER TABLE `influencers`
    CHANGE COLUMN `median_comments` `median_comments` DECIMAL(10,2) NOT NULL DEFAULT 0;

Upvotes: 9

Somy
Somy

Reputation: 1624

If you are using decimal(10,8) as data type it means you are specifying 8 digits after decimal place which leaves only (10 - 8 i.e 2 digits) for your whole number.

In this case since your number 1347 contains 4 digits (whole number) hence you are getting the error as "Out of range value" since you are allowed only 2.

You should consider changing it to at least decimal (12,8) which will leave you 4 digits for your whole number part and your above command should work.

Please refer to post - Number format issue in Oracle. Same issue.

Upvotes: 0

Related Questions