CODEWITHSUNDEEP
sqlsql-server
gh9
gh9

Reputation: 10693

SQL Server and implicit conversion of types

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

Upvotes: 6

Views: 12409

Answers (2)

Kaf
Kaf

Reputation: 33809

There is a chart here on MSDN (also shown below) which shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. Also it explains the conversions which are NOT allowed etc.

It explains 

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

Data type conversion table sql

Upvotes: 7

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

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).

Upvotes: 9

Related Questions