When an operator combines two expressions of different data types, the rules of data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operations will be that data type.
Here are a couple of examples on errors that are usually encountered due to the implicit conversion of data types based on the data type precedence outlined below:
SELECT 'The Current Date is ' + GETDATE()
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
SELECT @@ROWCOUNT + ' Rows Affected'
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' Rows Affected' to data type int.
SQL Server uses the following precedence for data types, from highest to lowest:
- User-defined data types
- sql_variant
- xml
- datetimeoffset
- datetime2
- datetime
- smalldatetime
- date
- time
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar (including nvarchar(max))
- nchar
- varchar (including varchar(max))
- char
- varbinary (including varbinary(max))
- binary