SQL Server 和类型的隐式转换

本文介绍了SQL Server 和类型的隐式转换的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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