SQL Server日期时间转换失败

问题描述：

我有一个有100万条记录的大表。不幸的是，创建桌子的人决定将日期放在varchar(50)字段中。SQL Server日期时间转换失败

我需要做一个简单的日期比较 -

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

@SQLMenace

我并不担心在这种情况下的性能。这将是一次性查询。将表格更改为日期时间字段不是一个选项。

@Jon Limjap

我已经尝试添加第三个参数，并没有什么区别。

@SQLMenace

的问题是最有可能的数据是如何存储的，只有两个安全的格式; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh：mm：ss：mmm（不包括空格）

isdate()检查是不是照顾这件事？

我不需要100％的准确性。我只想获得最近30天内的大部分记录。

@SQLMenace

select isdate('20080131') -- returns 1 
select isdate('01312008') -- returns 0

@Brian Schkerke

放置CONVERT（）函数内部的情况和ISDATE。

谢谢！那样做了。

您还没有提及varchar字段的日期格式。它们是否一致地格式化？了解格式将有助于解决问题。 – Kibbee 2008-08-26 14:33:10

答

将CASE和ISDATE放在CONVERT()函数内。

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' 与您所选择的默认日期。

答

我建议清理烂摊子，更改列于日期时间，因为做这样的东西

WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) < 31

不能使用索引，它会慢很多倍，如果你有一个日期时间科拉姆，正并做

where lastUpdate > getDate() -31

您还需要考虑到小时，当然

答

以秒为单位的转换通话，你需要指定第三样式paramete r，例如本文档中指定的以varchar存储的日期时间格式：CAST and CONVERT (T-SQL)

答

打印出记录。把硬拷贝给那个决定使用varchar（50）的白痴，让他们找到问题记录。

下次他们可能会看到选择适当数据类型的要点。

答

的问题是最有可能的数据是如何存储的，只有两个安全格式

ISO YYYYMMDD

ISO 8601 YYYY-MM-DD THH：MM：SS：嗯（无空格）

无论你的语言是什么，这些都可以工作。

你可能需要做一个SET DATEFORMAT YMD（或任何数据存储为），使其工作

答

如何通过内容写一个游标循环，尝试投为每个条目？

发生错误时，输出问题记录的主键或其他标识详细信息。

我想不出一套基于集合的方式来做到这一点。

编辑 - 啊是的，我忘了ISDATE（）。绝对比使用游标更好。 +1到SQLMenace。

答

isdate（）检查是否照顾这个？

运行这个，看看会发生什么

select isdate('20080131') 
select isdate('01312008')

答

如何通过内容写一个游标循环，尝试投为每个条目？当错误发生时，输出的主键或其他确定问题记录的详细信息。我想不出一套基于集合的方式来做到这一点。

不能完全setbased但如果只有3排了1万是坏它会为你节省大量的时间

select * into BadDates 
from Yourtable 
where isdate(lastUpdate) = 0 

select * into GoodDates 
from Yourtable 
where isdate(lastUpdate) = 1

然后就看BadDates表和修复

答

的如果确实首先执行了ISDATE（），它将处理未正确格式化的行。但是，如果你看执行计划，你可能会发现DATEDIFF谓词首先被应用 - 这是你痛苦的原因。

如果您使用SQL Server Management Studio中打CTRL +大号到查看估计的执行计划为特定的查询。

请记住，SQL不是一种过程式语言，并且短路逻辑可能工作，但前提是您小心如何应用它。

答

由于任何遗留系统需求，我确定更改表/列可能不是一个选项，但是如果您使用的是更新版本，是否想过创建一个包含日期转换逻辑的视图的SQL，那么你甚至可以使用索引视图？

SQL Server日期时间转换失败

相关推荐