转换为日期时间仅在 WHERE 子句上失败？

Question

我在执行某些 SQL Server 查询时遇到问题。事实证明，我有一个包含“Attibute_Name”和“Attibute_Value”字段的表，这些字段可以是任何类型，存储在 varchar 中。 （是的......我知道。）

特定属性的所有日期似乎都以“YYYY-MM-DD hh:mm:ss”格式存储（对此不能100%确定，这里有数百万条记录） ，所以我可以毫无问题地执行此代码：

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'

但是，如果我执行以下代码：

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

我将收到以下错误： 从字符串转换日期和/或时间时转换失败。

为什么在 where 子句上失败而不是在 select 子句上失败？

另一种线索：

如果我不使用 Attribute_Name 进行过滤，而是使用数据库 (PK) 中存储的实际 Attribute_ID，那么它将毫无问题地工作。

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_ID = 15
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

更新 谢谢大家的回答。我发现很难真正选择正确的答案，因为每个人都指出了一些对理解问题有用的东西。这肯定与执行顺序有关。 事实证明，我的第一个查询工作正常，因为先执行 WHERE 子句，然后执行 SELECT。 我的第二个查询由于相同的原因而失败（由于未过滤属性，因此在执行相同的 WHERE 子句时转换失败）。 我的第三个查询成功了，因为 ID 是索引 (PK) 的一部分，因此它优先，并且首先根据该条件向下钻取结果。

谢谢！

Answer 1

您似乎假设在 WHERE 子句中对谓词进行某种短路评估或保证排序。这是无法保证的。当您在列中混合数据类型时，处理它们的唯一安全方法是使用 CASE 表达式。

使用（例如）

CONVERT(DATETIME, 
      CASE WHEN ISDATE(pa.Attribute_Value) = 1 THEN pa.Attribute_Value END)

不使用

CONVERT(DATETIME, pa.Attribute_Value)

Answer 2

如果转换位于 WHERE 子句中，则可能会评估比出现在投影列表中更多的记录（值）。我之前已经在不同的上下文中讨论过这个问题，请参阅 T-SQL 函数并不暗示一定的执行顺序 和 关于 SQL Server 布尔运算符短路 。你的情况更简单，但很相似，最终的根本原因是相同的：在处理像 SQL 这样的声明性语言时，不要假设命令式执行顺序。

从很大程度上来说，最好的解决方案是清理数据并将列类型更改为 DATETIME 或 DATETIME2 类型。 所有其他解决方法都会有一个或另一个缺点，因此您最好只做正确的事情。

更新

仔细查看后（抱歉，我是@VLDB，只在会话之间查看）我意识到您有一个具有固有的无类型语义的 EAV 存储（attribute_value可以是字符串、日期、整数等）。我的观点是，最好的选择是在存储中使用 sql_variant 并一直到客户端（即项目 sql_variant）。您可以在客户端中凝聚类型，所有客户端 API 都有从 sql_variant 中提取内部类型的方法，请参阅 使用 sql_variant 数据（好吧，几乎所有客户端 API...使用 sql_variant 数据类型CLR）。使用sql_variant，您可以存储多种类型，而无需处理字符串表示形式的问题，您可以使用SQL_VARIANT_PROPERTY 来检查存储值中的 BaseType 之类的内容，您甚至可以像检查这样的想法强制数据类型正确性的约束。

Answer 3

这与 SELECT 查询的处理顺序有关。 WHERE 子句在 SELECT 之前很久就被处理。它必须确定要包含/排除哪些行。使用该名称的子句必须使用扫描来调查所有行，其中一些行不包含有效的日期/时间数据，而键可能会导致查找，并且此时不包含任何无效行。 SELECT 列表中的转换最后执行，显然此时它不会尝试转换无效行。由于您将日期/时间数据与其他数据混合，因此您可以考虑将日期或数字数据存储在具有正确数据类型的专用列中。同时，您可以通过以下方式推迟检查：

SELECT /* ... */
FROM
(
  SELECT /* ... */
    FROM ProductAttributes AS pa
    INNER JOIN dbo.Attributes AS a
    ON a.Attribute_ID = pa.Attribute_ID
    WHERE a.Attribute_Name = 'SomeDate'
    AND ISDATE (pa.Attribute_Value) = 1
) AS z
WHERE CONVERT(CHAR(8), AttributeValue, 112) < CONVERT(CHAR(8), GETDATE(), 112);

但更好的答案可能是如果可能的话使用 Attribute_ID 键而不是名称。

Answer 4

对我来说似乎是一个数据问题。当您使用两种不同的方法选择数据时，请查看数据，尝试寻找不同的长度，然后选择不同集合中的项目并观察它们。还检查空值吗？ （我不确定如果您尝试将 null 转换为日期时间会发生什么）

Answer 5

我认为问题是你的数据库中有一个错误的日期（显然）。

在第一个示例中，您没有检查 WHERE 子句中的日期，a.attribute.Name = 'SomeDate' 的所有日期都是有效的，因此它永远不会尝试转换错误的日期日期。

在第二个示例中，添加到 WHERE 子句会导致查询计划实际转换所有这些日期并找到错误的日期，然后查看属性名称。

在您的第三个示例中，更改为使用 Attribute_Id 可能会更改查询计划，以便它仅查找 id = 15 的记录，然后检查这些记录是否具有有效日期，它们确实如此。 （也许 Attribute_Id 已建立索引，而 Attribute_name 未建立索引）

因此，您在某处有一个错误的日期，但它不在 Arttribute_id = 15 的任何记录中。

Answer 6

您可以检查执行计划。可能是，在第一个查询中，第二个条件（ CONVERT(DATETIME, pa.Attribute_Value) < GETDATE() ）首先在所有行上进行评估，包括具有无效数据（不是日期）的行，而在第二种情况 - a.Attribute_ID = 15 首先被评估。因此排除具有非日期值的行。

顺便说一句，第二个可能也更快，如果您在选择列表中没有任何 Attributes 内容，则可以删除 inner join Attributes a on a.Attribute_ID = pa .Attribute_ID。

就这一点而言，建议摆脱 EAV，趁现在还不算太晚:)