>SQL Server: Float to Varchar Conversion


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.
SELECT @FloatVal = 421.765433
First lets check, how SQL Server implicitly converts float value to varchar
SELECT @VarcharVal = @FloatVal
SELECT @VarcharVal

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))
SELECT @VarcharVal
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.
 Yeah, that’s what we were looking for.



Posted on April 29, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 2 Comments.

  1. >Wow, literally I am big fan of this kind of post, short , clear and concise and yet effective to teach something valuable to you within few seconds. Thanks a lot mate and I hope to see more and more from you. great tips keep it up.Javin 10 Basic MySQL command for application developer

  2. According to msdn, format 128 is deprecated and might not be supported in future releases.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: