当不应该进行转换时 SQL 日期时间转换失败

我正在修改客户的现有查询，并且遇到了一个有点令人困惑的问题。

我们的客户使用 SQL Server 2008 R2，并且相关数据库使用户能够通过使用 EAV 结构为其表之一指定自定义字段。该结构中存储的所有值都是varchar(255)，其中几个字段用于存储日期。正在修改有问题的查询以使用其中两个字段，并将它们（一个是开始，另一个是结束）与当前日期进行比较，以确定哪一行是“当前”。

我遇到的问题是查询的一部分执行了CONVERT(DateTime, eav.Value)为了转动varchar into a DateTime。转换本身全部成功，我可以将该值包含在SELECT子句，但问题的一部分给了我一个转换错误：

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

真正的问题是：如果我定义此查询的基础（获取两个自定义字段值平铺到单行中的实体列表）作为视图，然后针对该视图进行选择并按以下条件过滤视图getdate()，那么它可以正常工作，但如果我使用视图中的（非日期）字段之一将联接添加到第二个表，则会失败。我意识到这可能有点难以理解，因此如果需要，我可以发布一个示例查询，但这个问题已经有点长了。

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

EDIT如果它有用，这里是视图的声明：

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 列）

我以前遇到过这种类型的错误，这是由于执行计划执行的“操作顺序”造成的。

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

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

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

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

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

即替换：

CONVERT(DATETIME,eav.Value)

with:

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

or:

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

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

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

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

我使用的另一种方法是尝试使用内联视图或公共表表达式来尝试对优化器的操作顺序进行一定程度的控制，并使用强制优化器具体化它们并应用谓词的操作，以便在之前发生这种情况外部查询中的任何转换。