CODEWITHSUNDEEP
mysql
sveti petar
sveti petar

Reputation: 3787

Numeric value out of range for decimal data type?

I have a column called closing_price which is DECIMAL(6,2). My understanding is that this means it can take 6 digits and 2 decimal places, so the largest number that could fit in this column would be 999999.99.

However, I am getting this error now:

Numeric value out of range: 1264 Out of range value for column 'closing_price' at row 1 (SQL: insert into `stock_history` (`date`, `closing_price`, `stock_id`, `updated_at`, `created_at`) values (2000-02-01, 51900.000000, 214, 2020-02-07 09:51:03, 2020-02-07 09:51:03))

I don't understand how this could fall out of range of DECIMAL(6,2). Please correct me if my understanding is wrong, or help me troubleshoot this if I am right.

I don't know why the number 51900 is showing zeros for 6 decimal places. I am getting these values from an API and a lot of them have up to 6 zeros at the end, but this is the first one that threw and error after over a million inserts.

Upvotes: 0

Views: 1895

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Your understanding is slightly off. DECIMAL(6,2) means that there are 6 total places of precision, with two of those places occurring after the decimal point. So, to store a value as large as 999999.99, you would actually need DECIMAL(8,2).

If you want a generally good type for storing currency values, you might follow the advice of this canonical SO answer, which suggests using DECIMAL(15,2).

Upvotes: 1

Related Questions