Search code examples
t-sqlcastingvarcharbigint

cast varchar to float to bigint

I am trying to cast varchar to bigint. Then inserting it into an int column. I found that I am not getting expected value. Then I tried the following statements:

DECLARE @varData VARCHAR(50)

SET @varData  = '0000019.33'

select cast(@varData *cast(100 as float) as bigint)

select cast(@varData *cast(100 as float) as varchar)

select cast(cast(@varData *cast(100 as float) as varchar) as float)

Results:

1932

1933

1933

I am not able to understand why this difference is coming. But when I change the value to 0000029.33 then there is no difference in results.

I am stuck and do not have any idea why it's so happening. Help needed..!!!

Solution

  • please see this Difference between numeric,float and decimal in sql server

    For you question, you should try CAST @vardata as numeric, like this

    SELECT CAST(CAST(@varData AS numeric(27,9)) * 100  AS bigint)