代码之家 › 专栏 › 技术社区 › scottm

我如何才能找出哪些数据无法转换?

sql-server-2000 tsql sql

scottm · 技术社区 · 16 年前

我有一个SQL表,其中日期字段定义为char(8)或20090609,时间字段定义为char(4)或1230。我正在将此数据移动到另一个表中,我希望合并这两个字段,并将它们放在新表中的smalldatetime字段中。我的查询如下:

INSERT NewTable(eventdate) 
SELECT
  CAST((datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2)) as smalldatetime)
FROM OldTable

当我运行此命令时,会得到一个错误:

char数据类型转换为 smalldatetime数据类型导致超出范围smalldatetime值。

我试过检查len(datecol)和len(timecol)以确保它们至少是正确的字符数。我不知道我怎样才能找到冒犯的数据,有什么建议吗?数据库是sql2000,我使用的是smo 2008。

3 回复 | 直到 16 年前

cmsjr 16 年前

它可能超出了可接受的smalldatetime值的范围。 1900年1月1日至2079年6月6日

编辑仔细检查后,我认为第二部分时间的子串参数可能不正确(可能是整个问题),在下面更新以反映子串(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)

D'Arcy Rittich 16 年前

试试这个:

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

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

-1

Rigobert Song 16 年前

如果在查询分析器中运行查询,它应该告诉您发生错误的行!