我正试图通过ODBC连接在Pervasive SQL数据库中获取表示美元值的值,并将其四舍五入到最接近的美分。然而,每当我运行TRUNCATE或ROUND函数时,我会在小数点后得到比预期更多的数字。
例如命令
select TRUNCATE(1234.12346345766,2), ROUND(1234.12346345766,2), TRUNCATE(ROUND(1234.12346345766,2),2);
返回以下
+------------------------------+---------------------------+---------------------------------------+
| TRUNCATE(1234.12346345766,2) | ROUND(1234.12346345766,2) | TRUNCATE(ROUND(1234.12346345766,2),2) |
+------------------------------+---------------------------+---------------------------------------+
| 1234.1199999999999 | 1234.1199999999999 | 1234.1099999999999 |
+------------------------------+---------------------------+---------------------------------------+
我所期望的是
+------------------------------+---------------------------+---------------------------------------+
| TRUNCATE(1234.12346345766,2) | ROUND(1234.12346345766,2) | TRUNCATE(ROUND(1234.12346345766,2),2) |
+------------------------------+---------------------------+---------------------------------------+
| 1234.12 | 1234.12 | 1234.12 |
+------------------------------+---------------------------+---------------------------------------+
我能做些什么来解决这个问题?
我自己想好了,但我没有找到任何其他帖子来回答这个确切的问题(如果有,请不要杀了我(。
看起来这是由于值被存储为双精度值而不是十进制值(由于我工作的环境的限制,我看不到表模式。(
有效的解决方案是通过将查询包装在CAST((函数中,将数据从DOUBLE转换为DECIMAL
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));
结果如下
+-----------------------------------------------------+--------------------------------------------------+--------------------------------------------------------------+
| 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 |
+-----------------------------------------------------+--------------------------------------------------+--------------------------------------------------------------+
这已经足够接近我想要的了。