我有一张有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

@SQLMenace

我不关心这种情况下的性能。这将是一次性查询。不能将表更改为日期时间字段。

乔恩·林贾普

我尝试添加第三个参数，但没有区别。

@SQLMenace

The problem is most likely how the data is stored, there are only two safe formats; ISO YYYYMMDD; ISO 8601 yyyy-mm-dd Thh:mm:ss:mmm (no spaces)

isdate()检查不会解决这个问题吗？

我不需要100％的准确性。我只想获取过去30天内的大部分记录。

@SQLMenace

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

@布莱恩·施可克(Brian Schkerke)

Place the CASE and ISDATE inside the CONVERT() function.

谢谢！做到了。

解决方案

回答

我建议清理混乱并将列更改为日期时间，因为这样做是

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

无法使用索引，它将比我们拥有datetime列的速度慢很多倍，并且

where lastUpdate > getDate() -31

当然，我们还需要考虑小时和秒

回答

在convert调用中，我们需要指定第三个样式参数，例如，存储在varchar中的日期时间格式，如本文档中所指定：CAST和CONVERT(T-SQL)

回答

打印出记录。将硬拷贝提供给决定使用varchar(50)的白痴，并要求他们查找问题记录。

下次他们可能只会看到选择适当数据类型的意义。

回答

问题很可能是数据的存储方式，只有两种安全格式

ISO YYYYMMDD

ISO 8601 yyyy-mm-dd Thh：mm：ss：mmm(无空格)

无论我们使用哪种语言，这些功能都可以使用。

我们可能需要执行SET DATEFORMAT YMD(或者存储的任何数据)才能使其正常工作

回答

如何编写一个游标以遍历目录内容，并尝试对每个条目进行强制转换？

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

我想不出基于集合的方法来执行此操作。

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

回答

将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"替换为我们选择的默认日期。

回答

Wouldn't the isdate() check take care of this?

运行这个看看会发生什么

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

回答

How about writing a cursor to loop through the contents, attempting the cast for each entry?When an error occurs, output the primary key or other identifying details for the problem record.
  I can't think of a set-based way to do this.

并非完全基于集合，但是如果每百万行中只有3行是坏的，它将为我们节省大量时间

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 + L查看特定查询的估计执行计划。

请记住，SQL不是一种过程语言，短路逻辑可能会起作用，但前提是我们必须谨慎使用它。

回答

我确信由于任何旧系统要求，都不可能更改表/列，但是我们是否考虑过创建一个内置日期转换逻辑的视图，如果我们使用的是较新版本的sql，那么我们甚至可以使用索引视图？