SQL Server and implicit conversion of types

Question

Where does SQL Server do implicit conversion and what are the rules it follows? I.E when does it convert the left side of the equality operator over 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 you are after DataType Precedence

In your examples:

WHERE quantity > '3'

'3' is cast to int, matching quantity

WHERE quantityTest > 3

No casting required

WHERE date = 20120101

20120101 as a number is being cast to a date, which is too large. e.g.

select cast(20120101 as datetime)

This is different from

WHERE date = '20120101'

Where the date as a string can be cast.

If you go down a third of the CAST and CONVERT reference to the section Implicit Conversions, there is a table of implicit conversions that are allowed. Just because it is allowed doesn't mean it will work, such as (20120101 -> datetime).