Changing decimal places of a Double value MySQL

Question

I have a database table called druginfo. It contains prices in WSprice column. Type of WSprice column is Double. This means It can contain like 23.5698 values. But I want to show all the values in the column WSprice 2 decimal places rounded like 23.57. How to apply that to all values in the column? Help me to do this.

http://stackoverflow.com/questions/7705548/mysql-how-can-i-always-round-up-decimals — Abhishek Ghosh, Aug 14 '15 at 06:36
possible duplicate of [Format number to 2 decimal places](http://stackoverflow.com/questions/11190668/format-number-to-2-decimal-places) — Tim Biegeleisen, Aug 14 '15 at 06:36

Tim Biegeleisen · Accepted Answer · 2015-08-14T06:55:56.013

8

If you only want to display your DOUBLE column to 2 decimal places, you can use the ROUND function:

SELECT ROUND(column_name, 2)
FROM your_table

This will display a value of 23.5698 as 23.57 in the result set.

If you want to change the format of the entire column you can use this:

ALTER TABLE your_table MODIFY column_name DECIMAL(9, 2)

edited Aug 14 '15 at 06:55

answered Aug 14 '15 at 06:36

Tim Biegeleisen

502,043
27
286
360

I just used an example as 23.5698 But in the column there are many values like that example @Tim Biegeleisen how to commonly change the decimal places to 2 of all values? – hinata Aug 14 '15 at 06:41
Do you want to _show_ 2 decimal places, or do you want to _change_ the entire column to be rounded to 2 decimal places? – Tim Biegeleisen Aug 14 '15 at 06:42
I want to change the entire column to be rounded to 2 decimal places. @Tim Biegeleisen – hinata Aug 14 '15 at 06:44
2

You should change your column to `DECIMAL(9,2)`, which is a fixed point type, unlike float - https://dev.mysql.com/doc/refman/5.5/en/fixed-point-types.html – Horia Aug 14 '15 at 06:50
And If your old data type is `DOUBLE` and you don't want to change it, just use `DOUBLE(9,2)` like `DECIMAL` this answer. – Van Tho Aug 06 '21 at 16:28

score 1 · Answer 2 · answered Oct 30 '19 at 07:32

I think we need to use CAST() instead of ROUND().

The reason behind is ROUND() return decimal values when decimal value exists in the database.

Example:

SELECT ROUND(columnName, 2)
FROM tableName

if columnName = 10.5 Output will be like 10.50

But if columnName = 10 Output will be like 10

And CAST() will return decimal value. But we need DECIMAL with CAST

SELECT CAST(columnName AS DECIMAL(10,2))
FROM tableName

The output will be like 10.00

Changing decimal places of a Double value MySQL

2 Answers2