CODEWITHSUNDEEP
mysqldecimalprecision
Rasool Ghafari
Rasool Ghafari

Reputation: 4258

MySQL change value of decimal(19, 2) when insert into database

I created table as you can see below:

create table sample_tbl
(
    decimal_column decimal(19,2) null
);

now, when i insert into this table with simple insert command:

insert into sample_tbl values (18.939);

MySQL stores it as 18.94, how should i fix this problem? i want to store real value: 18.93

Upvotes: 2

Views: 375

Answers (3)

Sandesh Mankar
Sandesh Mankar

Reputation: 699

  1. If it is DECIMAL(10, 2) it will have a total of 10 numbers of which 2 are decimal values (with 2 decimal rounding meaning that 10.215 is saved as 10.22 and 10.114 becomes 10.11).

  2. If it is DECIMAL(10) it will not have any decimal values and be rounded to an integer.

  3. If you use FLOAT or DOUBLE PRECISION you don't have to specify the number of decimal values but it has its own flaws.

Upvotes: 1

forpas
forpas

Reputation: 164089

The value that you insert is rounded correctly to 18.94.
What you want is to truncate the value to 2 decimal places, so use the function truncate():

insert into sample_tbl values (truncate(18.939, 2));

Upvotes: 1

Vora Ankit
Vora Ankit

Reputation: 722

update sample_tbl set decimal_column =truncate(decimal_column,2)

Upvotes: 1

Related Questions