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?
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.