SQL Server 和类型的隐式转换 [英] SQL Server and implicit conversion of types

问题描述

SQL Server 在哪里做隐式转换，遵循什么规则?即它何时将相等运算符的左侧转换为右侧?

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

推荐答案

这是您要查找的列表 数据类型优先级

在您的示例中:

WHERE quantity > '3'

'3' 被转换为 int，匹配数量

'3' is cast to int, matching quantity

WHERE quantityTest > 3

无需投射

WHERE date = 20120101

20120101 as a number 被转换为一个过大的日期.例如

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

select cast(20120101 as datetime)

这与

WHERE date = '20120101'

可以将日期转换为字符串.

如果您将 CAST 和 CONVERT 参考的三分之一转到隐式转换部分，有一个允许的隐式转换表.仅仅因为它被允许并不意味着它会起作用，例如 (20120101 -> datetime).

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

这篇关于SQL Server 和类型的隐式转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！