SQL Server and Implicit Type Conversion

Question

Where does SQL Server do implicit conversion and what rules does it follow? IE, when does it convert the left side of the equality operator on the right side?

  Foobar
 id int not null 
 quantity int not null
 quantityTest byte not null
 date varchar (20) not null
 dateTest datetime 

SELECT id FROM Foobar WHERE quantity > '3' SELECT id FROM foobar WHERE quantityTest > 3 Select id FROM foobar WHERE date = 20120101 

Answer 1

This is the list that you are after DataType Priority

In your examples:

 WHERE quantity > '3' 

'3' is converted to int, matching the number

 WHERE quantityTest > 3 

No casting required

 WHERE date = 20120101 

20120101 because the number is added to a date that is too large. eg.

 select cast(20120101 as datetime) 

It is different from

 WHERE date = '20120101' 

If the date as a string can be cast.

If you go to the third part of the CAST and CONVERT links in the Implicit Conversions section, the implicit conversion table is resolved. Just because it is allowed does not mean that it will work, for example (20120101 → datetime).

Answer 2

The following is a diagram here in MSDN (also shown below) that shows all the explicit and implicit data type conversions that are allowed for SQL Server system data types. He also explains conversions that are NOT allowed, etc.

It explains

 -Explicit Conversions -Implicit Conversions -Conversions not allowed ...