Converting SQL data types can be a bit finicky, and, at least for this guy, converting a stored, large integer value to a string is not intuitive at all.
I mostly run into this when I import values from some data source like an Excel sheet that stores values like tracking numbers as a float. From there, I usually write a cursor to update tables in my system with these values, and when those tables use a column type of varchar or nvarchar, you have to convert from float type to varchar
One would think that using CAST(varchar(50), TrackingNumber) would do the trick, but when this cast is made, the value is stored in scientific notation.
The real trick is to first convert the int value to a bigint and THEN convert it to a varchar, as shown below:
CONVERT(varchar(50), CONVERT(bigint, TrackingNumber))