转换为datetime仅在WHERE子句上失败？ [英] Conversion to datetime fails only on WHERE clause?

问题描述

我遇到一些SQL Server查询问题。原来我有一个表，其中包含Attibute_Name和Attibute_Value字段，可以是任何类型的，存储在varchar中。 （是啊...我知道。）

特定属性的所有日期似乎存储在YYYY-MM-DD hh：mm：ss格式（不是100％肯定，这里有数百万条记录），所以我可以执行这个代码没有问题：

  select /*...*/ CONVERT（DATETIME，pa.Attribute_Value）
从
 ProductAttributes pa 
内部连接属性a on.Attribute_ID = pa.Attribute_ID 
其中
 a.Attribute_Name ='SomeDate'
  

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

  select /*...*/ CONVERT（DATETIME，pa.Attribute_Value）
 from 
 ProductAttributes pa 
 inner join属性a onA.Attribute_ID = pa.Attribute_ID 
其中
 a.Attribute_Name ='SomeDate'
和CONVERT（DATETIME，pa.Attribute_Value）< GETDATE（）
  

我会收到以下错误：
转换失败转换日期和/或时间。

如何在where子句上失败，而不是选择一个？ / b>

另一个线索：

如果不是通过Attribute_Name进行过滤，实际的Attribute_ID存储在数据库（PK）中，它将工作没有问题。

  select /*...*/ CONVERT（DATETIME， pa.Attribute_Value）
从
 ProductAttributes pa 
内部连接属性a on.Attribute_ID = pa.Attribute_ID 
其中
 a.Attribute_ID = 15 
和CONVERT（DATETIME，pa.Attribute_Value）< GETDATE（）
  

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

谢谢

解决方案

如果转换在WHERE子句中，则可能会对其中的更多记录（值）进行评估，它出现在投影列表中。我以前在不同的背景下谈到过，见 T-SQL函数并不意味着一定的执行顺序和在SQL Server上布尔运算符短路。您的情况更简单，但是类似，最终的根本原因是相同的：在处理声明性语言（如SQL）时，不要承担命令式执行顺序。

您的最佳解决方案，远远大幅度地是清理数据，并将列类型更改为DATETIME或DATETIME2类型。 所有其他解决方法将有一个缺点或另一个缺点，所以你最好只是做正确的事情。

更新

仔细观察（对不起，我是@VLDB，只能在会话之间窥探SO）我意识到你有一个具有固有无类型语义的EAV存储（ attribute_value 可以是字符串，日期，int等）。我的意见是，你最好的办法是在存储中使用 sql_variant ，直到客户端（即项目 sql_variant ）。您可以在客户端中填充类型，所有客户端API都有从 sql_variant 中提取内部类型的方法，请参阅使用sql_variant数据（以及几乎所有客户端API ... ）中，使用sql_variant数据类型（在CLR中使用sql_variant数据类型）。使用 sql_variant 可以存储多个类型，以便通过字符串表示的问题，您可以使用 SQL_VARIANT_PROPERTY 来检查像 BaseType 存储的值，甚至可以像检查约束一样考虑强制数据类型的正确性。

I'm having a problem with some SQL server queries. Turns out that I have a table with "Attibute_Name" and "Attibute_Value" fields, which can be of any type, stored in varchar. (Yeah... I know.)

All the dates for a particular attribute seem to be stored the the "YYYY-MM-DD hh:mm:ss" format (not 100% sure about that, there are millions of records here), so I can execute this code without problems:

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'

However, if I execute the following code:

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

I will get the following error: Conversion failed when converting date and/or time from character string.

How come it fails on the where clause and not on the select one?

Another clue:

If instead of filtering by the Attribute_Name I use the actual Attribute_ID stored in database (PK) it will work without problem.

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

Update Thanks everyone for the answers. I found it hard to actually choose a correct answer because everyone pointed out something that was useful to understanding the issue. It was definitely having to do with the order of execution. Turns out that my first query worked correctly because the WHERE clause was executed first, then the SELECT. My second query failed because of the same reason (as the Attributes were not filtered, the conversion failed while executing the same WHERE clause). My third query worked because the ID was part of an index (PK), so it took precedence and it drilled down results on that condition first.

Thanks!

解决方案

If the conversion is in the WHERE clause it may be evaluated for many more records (values) than it would be if it appears in the projection list. I have talked about this before in different context, see T-SQL functions do no imply a certain order of execution and On SQL Server boolean operator short-circuit. Your case is even simpler, but is similar, and ultimately the root cause is the same: do not an assume an imperative execution order when dealing with a declarative language like SQL.

Your best solution, by a far and a large margin, is to sanitize the data and change the column type to a DATETIME or DATETIME2 type. All other workarounds will have one shortcoming or another, so you may be better to just do the right thing.

Update

After a closer look (sorry, I'm @VLDB and only peeking SO between sessions) I realize you have an EAV store with inherent type-free semantics (the attribute_value can bea string, a date, an int etc). My opinion is that your best bet is to use sql_variant in storage and all the way up to the client (ie. project sql_variant). You can coherce the type in the client, all client APIs have methods to extract the inner type from a sql_variant, see Using sql_variant Data (well, almost all client APIs... Using the sql_variant datatype in CLR). With sql_variant you can store multiple types w/o the problems of going through a string representations, you can use SQL_VARIANT_PROPERTY to inspect things like the BaseType in the stored values, and you can even do thinks like check constraints to enforce data type correctness.

这篇关于转换为datetime仅在WHERE子句上失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！