提问人：Seibar 提问时间：8/26/2008 最后编辑：CommunitySeibar 更新时间：8/26/2018 访问量：13954
SQL Server DateTime 转换失败
SQL Server DateTime conversion failure
问：
我有一个大桌子，上面有 100 万+ 条记录。不幸的是，创建表格的人决定将日期放在字段中。varchar(50)
我需要做一个简单的日期比较 -
datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31
但它在以下方面失败了：convert()
Conversion failed when converting datetime from character string.
显然，该领域有一些它不喜欢的东西，而且由于有这么多记录，我无法仅通过查看来判断。如何正确清理整个日期字段，使其不会在 ？这是我现在所拥有的：convert()
select count(*)
from MyTable
where
isdate(lastUpdate) > 0
and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31
在这种情况下，我不关心性能。这将是一个一次性查询。将表更改为日期时间字段不是一种选择。
我尝试添加第三个参数，这没有区别。
问题很可能是数据的存储方式，只有两种安全格式;国际标准化组织 YYYYMMDD;ISO 8601 yyyy-mm-dd Thh：mm：ss：mmm（无空格）
支票不会解决这个问题吗？isdate()
我不需要 100% 的准确性。我只想获取过去 30 天的大部分记录。
select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0
将 CASE 和 ISDATE 放在 CONVERT（） 函数中。
谢谢！做到了。
答：
0赞
SQLMenace
8/26/2008
#1
我建议清理混乱并将列更改为日期时间，因为做这样的事情
WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) < 31
不能使用索引，它会比你有 datetime 列，n 和 did 慢很多倍
where lastUpdate > getDate() -31
当然，您还需要考虑小时和秒
0赞
Jon Limjap
8/26/2008
#2
在转换调用中，您需要指定第三个样式参数，例如，存储为 varchar 的日期时间的格式，如本文档中指定： CAST 和 CONVERT （T-SQL）
0赞
Ian Nelson
8/26/2008
#3
打印出记录。把硬拷贝交给决定使用 varchar（50） 的白痴，并要求他们找到问题记录。
下次他们可能只是看到选择适当的数据类型的意义。
0赞
SQLMenace
8/26/2008
#4
问题很可能是数据的存储方式，只有两种安全格式
国际标准化组织 YYYYMMDD
ISO 8601 yyyy-mm-dd Thh：mm：ss：mmm（无空格）
无论您的语言是什么，这些都会起作用。
您可能需要执行 SET DATEFORMAT YMD（或存储数据的任何位置）才能使其正常工作
1赞
Ian Nelson
8/26/2008
#5
编写一个光标来遍历内容，尝试对每个条目进行强制转换怎么样？
发生错误时，输出问题记录的主键或其他标识详细信息。
我想不出一种基于集合的方法来做到这一点。
编辑 - 啊，是的，我忘记了 ISDATE（）。绝对是比使用光标更好的方法。+1 到 SQLMenace。
9赞
Disbeliever
8/26/2008
#6
将 和 放在函数内部。CASEISDATECONVERT()
SELECT COUNT(*) FROM MyTable
WHERE
DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate)
WHEN 1 THEN lastUpdate
ELSE '12-30-1899'
END), GetDate()) < 31
替换为您选择的默认日期。'12-30-1899'
0赞
SQLMenace
8/26/2008
#7
isdate（） 检查不会解决这个问题吗？
运行此操作以查看会发生什么
select isdate('20080131')
select isdate('01312008')
3赞
SQLMenace
8/26/2008
#8
编写一个光标来遍历内容，尝试对每个条目进行强制转换怎么样？发生错误时，输出问题记录的主键或其他标识详细信息。 我想不出一种基于集合的方法来做到这一点。
不是完全基于设置的，但如果 100 万行中只有 3 行是坏的，它会为您节省大量时间
select * into BadDates
from Yourtable
where isdate(lastUpdate) = 0
select * into GoodDates
from Yourtable
where isdate(lastUpdate) = 1
然后只需查看 BadDates 表并修复它
2赞
Disbeliever
8/26/2008
#9
ISDATE（） 将处理格式不正确的行，如果它确实是首先执行的。但是，如果您查看执行计划，您可能会发现首先应用了 DATEDIFF 谓词 - 因此是您痛苦的原因。
如果使用 SQL Server Management Studio，请按 + 查看特定查询的估计执行计划。CTRLL
请记住，SQL 不是一种过程语言，短路逻辑可能会起作用，但前提是你要小心应用它。
0赞
chris raethke
3/14/2009
#10
我敢肯定，由于任何遗留系统要求，更改表/列可能不是一个选项，但是您是否考虑过创建一个内置了日期转换逻辑的视图，如果您使用的是更新版本的 sql，那么您甚至可以使用索引视图？
评论