SQL Server and implicit conversion of types
sql, sql-server
Solution
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).
Problem
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 ```