Recently, someone asked that, if we convert float value to varchar then result is truncated and what is the best way to get whole value converted into varchar without any truncation.
DECLARE @FloatVal FLOAT
SELECT @FloatVal = 421.765433
First lets check, how SQL Server implicitly converts float value to varchar
DECLARE @VarcharVal VARCHAR(50)
SELECT @VarcharVal = @FloatVal
OOPS. Implicit conversion from float to varchar is NOT according to our required output. Let convert it explicitly and then assign this value to varchar variable.
SELECT @VarcharVal = CAST(@FloatVal AS VARCHAR(50))
You will observe same truncated value as a result of above query. Finally,let’s try it with convert as convert supports some formatting too.
SELECT CONVERT (VARCHAR(50), @FloatVal,128)
Yeah, that's what we were looking for.