sql-server - 从字符串转换日期和/或时间时转换失败 - SQL Server 错误

Question

我正在尝试根据表名称从数据库中动态选择表，而表名称又基于创建日期。例如，这些表可能名为“tableA20110305”或“tableB20110305”，表示这些表是于 2011 年 3 月 5 日创建的。

我正在尝试编写一个查询，该查询将选择在某个截止日期(1 年前)之前创建的所有以此命名的表，并将它们连接到表变量中的 DROP TABLE 命令语句中。 select 语句如下所示。

DECLARE @cutoffDate datetime = CONVERT(DATETIME, DATEADD(YEAR,-1,GETDATE()), 112)

SELECT 'DROP TABLE "' + TABLE_NAME + '"' AS 'Command'
    FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_NAME LIKE 'tableA%' OR TABLE_NAME LIKE 'tableB%')
    AND (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)
    ORDER BY Command DESC

但是，当我执行此 SQL 时，我看到以下错误:

Msg 241, Level 16, State 1, Line 14 Conversion failed when converting date and/or time from character string.

但是...如果我执行以下 SQL 语句，我看不到任何错误并按预期返回日期:

SELECT CONVERT(DATETIME, SUBSTRING('tableA20110305', 7, 8), 112)

我不明白为什么这些查询没有返回相同的结果，也不明白这个错误来自哪里。我非常感谢任何见解..

Answer 1

这很好地解释了这种行为。取自70-461: Querying Microsoft SQL Server 2012 :

WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10 

Suppose that the table being queried holds different property values. The propertytype column represents the type of the property (an INT, a DATE, and so on), and the propertyval column holds the value in a character string. When propertytype is 'INT', the value in propertyval is convertible to INT; otherwise, not necessarily.

Some assume that unless precedence rules dictate otherwise, predicates will be evaluated from left to right, and that short circuiting will take place when possible. In other words, if the first predicate propertytype = 'INT' evaluates to false, SQL Server won’t evaluate the second predicate CAST(propertyval AS INT) > 10 because the result is already known. Based on this assumption, the expectation is that the query should never fail trying to convert something that isn’t convertible.

The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well. This means that if there are rows in the table where propertytype is different than 'INT', and in those rows propertyval isn’t convertible to INT, the query can fail due to a conversion error.

在您的情况下，引擎决定首先按日期部分进行过滤，但失败了。

可以有几种解决方法:

1. 使用TRY_CAST相反(由 SQL Server 2012 提供支持)

2. 首先选择所有类似于 'tableA%' OR TABLE_NAME LIKE 'tableB%' 的表进入一些临时表，然后执行另一个过滤器 (CONVERT(DATETIME, SUBSTRING(TABLE_NAME, 7, 8), 112) < @cutoffDate)