CODEWITHSUNDEEP
sqlsql-servert-sql
Varun Shekhar RC
Varun Shekhar RC

Reputation: 13

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

Upvotes: 0

Views: 152

Answers (3)

Meyyappan
Meyyappan

Reputation: 344

you can use ceiling or floor inbuilt function based on your requirement

DECLARE @Escalator float = 0.05
DECLARE @Base float = 1.66
Select ((3.05-1.66)/0.05) --returns 27.8
Select ceiling (((3.05-@Base)/@Escalator)) -- returns 28
Select floor (((3.05-@Base)/@Escalator)) -- returns 27

Upvotes: 0

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

You can use Decimal to get rid of the issue

DECLARE @Escalator DECIMAL(7, 5) = 0.05
Select ((3.05-1.15)/0.05) --returns 38
Select CAST(((3.05-1.15)/@Escalator) AS INT) -- returns 38

Demo on db<>fiddle

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

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.

Upvotes: 1

Related Questions