When combining Data Types it is important to note that there is a data type precedence used to determine how they are converted and combined. Data types with a lower precedence are implicitly converted to the data type with the higher precedence.
If a data type of a higher precedence is needed to be converted to a data type of a lower precedence then an explicit conversion must be made using either the CAST or CONVERT functions.
Let’s first take a look at an implicit conversion where we are adding the numbers 42 + 11 which should add to 53. But then we have the value of ’25’ which is not an integer, but a character data type. Since the character variable can be converted to an integer; SQL will perform an implicit conversion and will display the following result.
Next, we will try where we have actual text that cannot be converted into an integer. Since a character data type has a lower precedence than an integer and SQL cannot perform the implicit conversion and will display the following result.
So to combine the text to the integer, the integer will have to be explicitly converted using either the CAST or CONVERT functions. The main difference between the CAST and CONVERT functions is that CAST is an ISO standard whereas CONVERT is specific to Transact-SQL and can also use styles for numbers and dates. In both cases, the following result will be displayed.
Here is an excellent conversion chart that will help in determining which data type can or cannot be converted.