从字符串转换日期和/或时间时错误转换失败: varchar(8000)
我有一个varchar(8000)类型的列。我们保存的各种数据，如数字，文字，日期等。
我编写了一个简单的select查询来获取表all中的所有“日期”值，我也可以使用CAST或Convert来获得这种格式的2014-12-16 00:00:00.000值。
现在我试图添加一个where子句来过滤今年的信息，但是我得到了一个错误：
从字符串转换日期和/或时间时，转换失败。
当我在选择查询中使用TOP 10000时，错误就消失了。这真的很奇怪
; WITH TempTable AS
(
SELECT
ID, CAST(Value AS DateTime) [SomeDate]
FROM
SampleTable
WHERE
ColType = 'Date'
)
SELECT *
FROM FROM TempTable
WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'这个扔了
从字符串转换日期和/或时间时，转换失败。
; WITH TempTable AS
(
SELECT TOP 100000
ID, CAST(Value AS DateTime) [SomeDate]
FROM
SampleTable
WHERE
ColType = 'Date'
)
SELECT *
FROM FROM TempTable
WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'这个很好用。请注意，我的结果中只有25行。前100000只是我用过的一个大数字。这是非常奇怪的，我的TOP关键字是如何使我的查询良好。
更新：
以下是我如何在不使用TOP关键字的情况下进行解析。我已经拆分了我的查询，如下所示。谢谢大家的时间和意见。
;WITH GetAllIDs
AS (
SELECT ID
FROM SampleTable
WHERE ColType = 'Date'
)
,FinalTable
AS (
SELECT ID
,(
SELECT Cast([Value] AS DATETIME)
FROM SampleTable dt
WHERE dt.ID = tt.ID
AND dd.ColType = 'Date'
) [SomeDate]
FROM GetAllIDs tt
)
SELECT *FROM FinalTable
WHERE [SomeDate] BETWEEN '1/1/2017' AND '12/30/2017'回答 3
Stack Overflow用户
发布于 2017-03-29 15:14:56
SQL语句中没有执行顺序。因此，where子句在转换值之前对行进行(必然)筛选。所以你的问题。
在Server 2012+中，有一个使用try_convert()的简单解决方案
With TempTable AS (
select ID, try_convert(datetime, Value) as SomeDate
from SampleTable
where ColType = 'Date'
)
Select *
from TempTable
Where SomeDate between '2017-01-01' and '2017-12-30';Microsoft认为这种行为是优化器的特性，而不是bug (我不同意)。它为优化查询提供了更多的机会。在这种情况下，简单的转换是在读取数据时完成的，而不是在处理管道下进行的。
注意，我还将日期常量更改为YYYY DD格式。您应该始终使用YYYY DD或YYYYMMDD作为格式。
当添加TOP时，这一点就消失了，这是执行计划的一些奇怪的工件。由于某些原因，这将在计算表达式之前进行筛选。我知道TOP有时会产生这种效果。在这种情况下，我很惊讶，因为查询很简单。
编辑：
在Server 2008中，可以使用case。就像这样：
With TempTable AS (
select ID, (case when isdate(value) = 1 then convert(datetime, Value) end) as SomeDate
from SampleTable
where ColType = 'Date'
)
Select *
from TempTable
Where SomeDate between '2017-01-01' and '2017-12-30';case表达式保证了参数的计算顺序。when只在then之后计算。
Stack Overflow用户
发布于 2017-03-29 15:49:47
基于SQL 2008，您必须更改模式。
首先，它使用TOP命令的原因。TOP选择与查询中的WHERE子句匹配的所有记录，但不超过顶部的限制。由于您有WHERE子句，顶部的版本只会将带有“date”的Coltype记录传递到日期检查。如果没有顶部，优化器认为根据日期范围检查值列的值要比过滤列类型要快，因为只需要一个数据元素检索就可以检查查询的“加速”(在本例中是爆破)。
应该工作的一个选项是将TempTable定义代码更改为：
; WITH TempTable AS
(
SELECT TOP 100 PERCENT
ID, CAST(Value AS DateTime) [SomeDate]
FROM
SampleTable
WHERE
ColType = 'Date'
)另一个明显的选项是将所有值提取到实际的临时表中，而不是直接使用CTE，然后查询该表。
Stack Overflow用户
发布于 2017-04-04 14:20:31
请看我的更新:这就是我如何解决我的问题。谢谢戈登，为你的评论笑维吉尔。
https://stackoverflow.com/questions/43097458
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号