Truncation using round function isn't achieved as expected in sql server

Question

I have a field stored in float datatype. I need to convert it to numeric without it getting implicitly rounded in the process of conversion.

I have tried round(float_data,scale,1). Seems to work fine for most of the cases.but when the number of digits after decimal places is less than scale mentioned in round function it tries to floor down the number rather than appending 0 at the end.

For instance, round (0.0243,5,1) returns 0.02429. Why isn't it simply truncating the number to the number of digits mentioned?

I know this issue is when we use float as source datatype but I cannot change the source datatype.

The same truncation happens right when the same is achieved via ssis. Is there any way in sql to achieve this?

Answer 1

Because when converted to a float, the decimal 0.0243 is stored as 0.02429999969899654388427734375, which truncates to 0.02429. Looks like you want to round instead of truncate, eg

declare @f float = 0.0243
select round(@f,5,0)