When combining Data Types, it is important to note that there is a data type precedence used. This is 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.

Implicit Conversion

--Implicit Conversion
SELECT 42 + 11 + '25' AS 'Result'

Explicit Conversion

What do we do if a data type of a higher precedence is needed to be converted to a lower precedence? Then an explicit conversion must be made using either the CAST or CONVERT functions.

Let us 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, SQL cannot perform the implicit conversion and will display the following result.

--Conversion Error
SELECT 42 + 11 + 'Total' AS 'Result'

CAST and CONVERT Functions

So, to combine the text to the integer, the integer will have to be explicitly converted. We can use 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.

--Explicit Conversion using CAST
SELECT CAST(42 + 11 as char(2)) + ' Total' AS 'Result'

--Explicit Conversion using CONVERT
SELECT CONVERT(char(2), 42 + 11) + ' Total' AS 'Result'

Conversion Chart

Here is an excellent conversion chart that will help in determining which data type can or cannot be converted.