Is there a way to concatenate different data types in TSQL that does not require me to convert everything to the same data type.
The way most us are used to concatenating data together is using the + sign. The problem we run into using this method is it expects the different items being concatenated together to be of the same data type. You have to perform an explicit conversion if they are not the same data type. The following example is trying to concatenate a INT and a String together, you can see SQL cannot handle the different data types.
The new CONCAT() function does an implicit conversion to string for all items being concatenated together. The other way, which requires more coding is to perform an explicit conversion and use the + method for concatenation. Below is an example of both methods where the result returns without errors. The first PRINT statement uses CONCAT() where an implicit conversion is performed and everything is converted to a string and then concatenated together. The second PRINT uses the + method for concatenation where you have to explicitly convert the INT to a String to get it to return without error.
The next example will be showing DATETIME concatenated with a String. This screenshot is using the + method for concatenation and it gives an error trying to concatenate a String with a DATETIME.
In summary, the CONCAT() function performs an implicit conversion of everything to a string before concatenating it together and returning the result. The + method for concatenation will perform an implicit conversion between string data types only, but if the data types are not of the same type, an explicit conversion has to be performed to achieve a successful result.