在SQL Server 2008中查找无效日期 [英] Find invalid dates in SQL Server 2008

问题描述

我有一个300.000行表;其中一列是一个varchar（），但它真的包含一个日期xx / xx / xxxx或x / x / xxxx或类似的。但是执行以下测试会产生错误：

  SELECT CAST（MyDate as Datetime）FROM MyTable 
  

问题是它不告诉我哪一行...

我已经通过试用一系列的手动更新错误，并执行简单的更新来修复这些错误，但必须要有一些奇怪的值需要被删除或修复。

例如，我执行了一个固定约40行的简单测试：

 更新MyTable SET MyDate = REPLACE（MyDate， '/ 000'，'/ 200'）FROM MyTable WHERE MyDate like（'％/ 000％'）
更新MyTable SET MyDate = REPLACE（MyDate，'/ 190'，'/ 199'）FROM MyTable WHERE MyDate喜欢（'％/ 190％'）
  

这固定了很多奇怪的行， / 01/0003等。 （日期从1998年到2010年）。

但是，我想知道哪些行在上述选择中失败。

打印这些的最好方式是什么，我可以删除它们，编辑它们或看看该怎么做？谢谢。

解决方案

  SELECT 
 * 
 FROM 
 MyTable 
 WHERE 
 ISDATE（MyDate）= 0 
  

I have a 300.000 rows table; one of the columns is a varchar() but it really contains a date xx/xx/xxxx or x/x/xxxx or similar. But executing the following test yields an error:

SELECT CAST(MyDate as Datetime) FROM MyTable

The problem is that it doesn’t tell me in which row…

I have executed a series of "manual" updates by trial an error and performed simple updates to fix those, but there’s got to be some weird values that need to either be deleted or fixed.

For example I performed a simple test that fixed about 40 rows:

UPDATE MyTable SET MyDate = REPLACE(MyDate, '/000','/200') FROM MyTable WHERE MyDate like ('%/000%’)
UPDATE MyTable SET MyDate = REPLACE(MyDate, '/190','/199') FROM MyTable WHERE MyDate like ('%/190%’)

This fixed quite a few weird rows that had dates like 01/01/0003 and such. (Dates range from 1998 to 2010).

However, I’d like to know which rows are failing in the above select.

What would be the best way to print those so I can either delete them, edit them or see what to do? Thanks.

解决方案

SELECT
    *
FROM
   MyTable
WHERE
    ISDATE(MyDate) = 0

这篇关于在SQL Server 2008中查找无效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！