时间戳

首页 > 解决方案 > Output of cast function

sql - Output of cast function

问题描述

I am working on the usage of the Cast function in SQL Server. If I use explicit value as written in the code. The output is 38 which is the correct value. But I would need to use field names instead of direct values(There is only value of Base, escalator here ; Base =1.15 and escalator=0.05. But when I use field names, the output is 37. The data type of Base and escalator fields is float. I also tried using round function inside cast, did not solve the issue. Could someone out here help me with this. My query below:

Select CAST((3.05-1.15)/0.05 AS INT) -- returns 38
Select ((3.05-1.15)/0.05) --returns 38
Select cast((3.05-base)/Escalator) as int) from table1 -- I am using field names here. Returns 37

标签： sqlsql-servertsql

解决方案

What is likely happening here is that the base and Escalator columns are some kind of non exact floating point type. As a result, the following computation results a value which is slightly less than 38:

(3.05-base) / Escalator = 37.999995 (for example)

Then, when casting to integer, the entire decimal component is being truncated, leaving behind just 37.

One possible workaround to prevent this from happening would be to use NUMERIC or some other exact type for the base and Escalator columns.

推荐阅读