SQL Server convert issues part 1 – int to varchar

Sometimes you need to convert a datatype to a different datatype in SQL Server. The reason might be to able to compare a column with a variable or another column with a different datatype. When you convert a datatype, you can get some unexpected results, like when you convert an int to a varchar and use a shorter varchar than the value you are trying to convert. Take a look at the code below.

DECLARE @myInt INT = 1111111;
SELECT ConvertedValue = CONVERT(VARCHAR(6), @myInt), OriginalValue = @myInt;

As you can see from the result, the converted value couldn’t fit in a VARCHAR(6) and you get an asterix instead. This is documented behaviour in SQL Server Books online.

Feel free to contact me or any of my other SQL server consultants if you like to know more about SQL Server or T-SQL

/Håkan Winther
Twitter: @h_winther