Truncate and Round giving more Decimals than expected in MySQL

Question

I'm trying to take values in a Pervasive SQL database through an ODBC connection that represent dollar values and round them to the nearest cent. However whenever I run the TRUNCATE or ROUND functions, I get more digits after the decimal place than expected.

For example the command

select TRUNCATE(1234.12346345766,2), ROUND(1234.12346345766,2), TRUNCATE(ROUND(1234.12346345766,2),2);

returns the following

+------------------------------+---------------------------+---------------------------------------+
| TRUNCATE(1234.12346345766,2) | ROUND(1234.12346345766,2) | TRUNCATE(ROUND(1234.12346345766,2),2) |
+------------------------------+---------------------------+---------------------------------------+
|           1234.1199999999999 |        1234.1199999999999 |                    1234.1099999999999 |
+------------------------------+---------------------------+---------------------------------------+

Where as what I would expect is something like

+------------------------------+---------------------------+---------------------------------------+
| TRUNCATE(1234.12346345766,2) | ROUND(1234.12346345766,2) | TRUNCATE(ROUND(1234.12346345766,2),2) |
+------------------------------+---------------------------+---------------------------------------+
|                      1234.12 |                   1234.12 |                               1234.12 |
+------------------------------+---------------------------+---------------------------------------+

What can I do to fix this?

score 0 · Accepted Answer · answered Mar 05 '20 at 19:29

I figured it out myself but I didn't find any other post answering this exact question (please don't kill me if there is).

Looks like this is due to the values being stored as doubles rather than as decimal values (due to the limitations of the environment I'm working in I can't see the table schema.)

The solution that worked was to transform the data from DOUBLE to DECIMAL by wrapping the query in the CAST() function

select CAST(TRUNCATE(1234.12346345766,2) as DECIMAL(10,2));
select CAST(ROUND(1234.12346345766,2) as DECIMAL(10,2));
select CAST(TRUNCATE(ROUND(1234.12346345766,2),2) as DECIMAL(10,2));

With the following result

+-----------------------------------------------------+--------------------------------------------------+--------------------------------------------------------------+
| CAST(TRUNCATE(1234.12346345766,2) as DECIMAL(10,2)) | CAST(ROUND(1234.12346345766,2) as DECIMAL(10,2)) | CAST(TRUNCATE(ROUND(1234.12346345766,2),2) as DECIMAL(10,2)) |
+-----------------------------------------------------+--------------------------------------------------+--------------------------------------------------------------+
|                                             1234.12 |                                          1234.12 |                                                      1234.11 |
+-----------------------------------------------------+--------------------------------------------------+--------------------------------------------------------------+

Which is close enough to what I wanted.

Truncate and Round giving more Decimals than expected in MySQL

1 Answers1