tsql - SQL 日期时间转换在不应发生转换时失败

Question

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

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

我遇到的问题是查询的一部分执行 CONVERT(DateTime, eav.Value)为了转varchar成DateTime .转换本身都成功了，我可以将该值作为 SELECT 的一部分包含在内。条款，但问题的一部分是给我一个转换错误:

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

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

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

编辑 如果它有用，这里是 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 列)

Answer 1

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

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

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

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

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

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

也就是说，替换:

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() 函数受到一些重大限制，例如受 session 的 DATEFORMAT 和 LANGUAGE 设置的影响。

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

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

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