我怎样才能知道哪些数据不会被转换?

试试这个:

SELECT datecol, timecol
FROM OldTable
WHERE ISDATE(datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 2, 2)) = 0

这将显示哪些行无法成功转换。

仔细检查后,我认为第二部分时间的子字符串参数可能不正确(这可能是整个问题),更新如下以反映子字符串(timecol,3,2)

新方法 这个sql确实假设所有日期的长度都是8个字符,所有时间都是4个字符。

Select SubString(DateCol, 1, 4) as tehYear, 
Substring(DateCol, 5,2) as tehMonth, 
SubString(DateCol, 7,2) as tehDay,
SubString(TimeCol, 1,2) as tehHour,
Substring(TimeCOl, 3,4) as tehMinute,
*
from OldTable
where
(SubString(DateCol, 1,4) > 9999 or SubString(DateCol, 1,4) < 1753)
OR (Substring(DateCol, 5,2) > 12 or Substring(DateCol, 5,2) < 1)
OR (SubString(DateCol, 7,2) > 31 or SubString(DateCol, 7,2) < 1)
OR (SubString(TimeCol, 1,2) > 23 or(SubString(TimeCol, 1,2) < 0)
OR (Substring(TimeCOl, 3,4) > 59 or Substring(TimeCOl, 3,4) <0)

尝试转换为datetime,查看是否有任何日期超出该范围,以识别您的问题数据。

    SELECT
    CAST((datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2))
    as datetime)
    FROM OldTable 
    Where CAST((datecol + ' ' + substring(timecol, 1, 2) 
    + ':' + substring(timecol, 3, 2)) as datetime) 
    > Cast('06/06/2079' as datetime) or CAST((datecol + ' ' 
    + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2)) as datetime)
    < Cast('01/01/1900' as datetime)

如果你在查询分析器中运行查询,它应该会告诉你错误发生在哪一行!