不应进行转换时 SQL 日期时间转换失败 [英] SQL DateTime Conversion Fails when No Conversion Should be Taking Place

问题描述

我正在为客户修改现有查询，但遇到了一个有点莫名其妙的问题.

I'm modifying an existing query for a client, and I've encountered a somewhat baffling issue.

我们的客户端使用 SQL Server 2008 R2，并且该数据库为用户提供了通过使用 EAV 结构为其其中一个表指定自定义字段的能力.此结构中存储的所有值都是 varchar(255)，其中一些字段用于存储日期.正在修改有问题的查询以使用其中两个字段并将它们(一个是开始，另一个是结束)与当前日期进行比较，以确定哪一行是当前".

Our client uses SQL Server 2008 R2 and the database in question provides the user the ability to specify custom fields for one of its tables by making use of an EAV structure. All of the values stored in this structure are varchar(255), and several of the fields are intended to store dates. The query in question is being modified to use two of these fields and compare them (one is a start, the other is an end) against the current date to determine which row is "current".

我遇到的问题是查询的一部分执行 CONVERT(DateTime, eav.Value) 以便将 varchar 转换为 日期时间.转换本身都成功了，我可以将该值包含在 SELECT 子句中，但部分问题是给我一个转换错误:

The issue I'm having is that part of the query does a CONVERT(DateTime, eav.Value) in order to turn the varchar into a DateTime. The conversions themselves all succedd and I can include the value as part of the SELECT clause, but part of the question is giving me a conversion error:

Conversion failed when converting date and/or time from character string.

真正的关键是:如果我将此查询的基础定义为视图(获取具有两个自定义字段值平展为单行的实体列表)并针对视图进行选择并通过 getdate()，然后它可以正常工作，但是如果我使用视图中的(非日期)字段之一将连接添加到第二个表，它会失败.我意识到这可能有点难以理解，所以如果需要，我可以发布一个示例查询，但这个问题已经有点长了.

The real kicker is this: if I define the base for this query (getting a list of entities with the two custom field values flattened into a single row) as a view and select against the view and filter the view by getdate(), then it works correctly, but it fails if I add a join to a second table using one of the (non-date) fields from the view. I realize that this might be somewhat hard to follow, so I can post an example query if desired, but this question is already getting a little long.

我尝试在另一个数据库中重新创建基本结构并包含示例数据，但新数据库的行为与预期一样，所以我在这里不知所措.

I've tried recreating the basic structure in another database and including sample data, but the new database behaves as expected, so I'm at a loss here.

编辑如果有用，这里是视图的声明:

EDIT In case it's useful, here's the statement for the view:

create view Festival as 
select
    e.EntityId as FestivalId,
    e.LookupAs as FestivalName,
    convert(Date, nvs.Value) as ActivityStart,
    convert(Date, nve.Value) as ActivityEnd

from tblEntity e

left join CustomControl ccs on ccs.ShortName = 'Activity Start Date'
left join CustomControl cce on cce.ShortName = 'Activity End Date'
left join tblEntityNameValue nvs on nvs.CustomControlId = ccs.IdCustomControl and nvs.EntityId = e.EntityId
left join tblEntityNameValue nve on nve.CustomControlId = cce.IdCustomControl and nve.EntityId = e.EntityId

where e.EntityType = 'Festival'

失败的查询是这样的:

select * 

from Festival f

join FestivalAttendeeAll fa on fa.FestivalId = f.FestivalId

where getdate() between f.ActivityStart and f.ActivityEnd

但这是可行的:

select * 

from Festival f

where getdate() between f.ActivityStart and f.ActivityEnd

(EntityId/FestivalId 是 int 列)

(EntityId/FestivalId are int columns)

推荐答案

我之前也遇到过这种类型的错误，是由于执行计划执行的操作顺序"造成的.

I've encountered this type of error before, it's due to the "order of operations" performed by the execution plan.

您收到该错误消息是因为您的语句的执行计划(由优化器生成)正在对包含无法转换为 DATETIME 的字符串值的行执行 CONVERT() 操作.

You are getting that error message because the execution plan for your statement (generated by the optimizer) is performing the CONVERT() operation on rows that contain string values that can't be converted to DATETIME.

基本上，您无法控制优化器在哪些行上执行该转换.您知道您只需要对某些行进行转换，并且您有排除这些行的谓词(WHERE 或 ON 子句)(将行限制为需要转换的行)，但您的执行计划正在执行 CONVERT() 操作在这些行被排除之前的行上.

Basically, you do not have control over which rows the optimizer performs that conversion on. You know that you only need that conversion done on certain rows, and you have predicates (WHERE or ON clauses) that exclude those rows (limit the rows to those that need the conversion), but your execution plan is performing the CONVERT() operation on rows BEFORE those rows are excluded.

(例如，优化器可能会选择执行表扫描，并在应用任何谓词之前对每一行执行该转换.)

(For example, the optimizer may be electing to a do a table scan, and performing that conversion on every row, before any predicate is being applied.)

如果没有具体的问题和产生错误的具体 SQL，我无法给出具体的答案.

I can't give a specific answer, without a specific question and specific SQL that is generating the error.

解决该问题的一种简单方法是使用 ISDATE() 函数来测试字符串值是否可以转换为日期.

One simple approach to addressing the problem would be to use the ISDATE() function to test whether the string value can be converted to a date.

即替换:

CONVERT(DATETIME,eav.Value)

与:

CASE WHEN ISDATE(eav.Value) > 0 THEN CONVERT(DATETIME, eav.Value) ELSE NULL END

或:

CONVERT(DATETIME, CASE WHEN ISDATE(eav.Value) > 0 THEN eav.Value ELSE NULL END)

请注意，ISDATE() 函数受到一些重大限制，例如受会话的 DATEFORMAT 和 LANGUAGE 设置的影响.

Note that the ISDATE() function is subject to some significant limitations, such as being affected by the DATEFORMAT and LANGUAGE settings of the session.

如果 eav 行上有其他指示，您可以使用其他测试来有条件地执行转换.

If there is some other indication on the eav row, you could use some other test, to conditionally perform the conversion.

CASE WHEN eav.ValueIsDateTime=1 THEN CONVERT(DATETIME, eav.Value) ELSE NULL END

<小时>

我使用的另一种方法是尝试使用内联视图或公用表表达式对优化器的操作顺序进行一些控制，这些操作会强制优化器实现它们并应用谓词，所以这发生在外部查询中的任何转换之前.

---

The other approach I've used is to try to gain some modicum of control over the order of operations of the optimizer, using inline views or Common Table Expressions, with operations that force the optimizer to materialize them and apply predicates, so that happens BEFORE any conversion in the outer query.

这篇关于不应进行转换时 SQL 日期时间转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！