关于转换日期格式时碰到的奇葩问题

[Nynas]

在SQL中有一个字段：

现在需要将其转换为datetime，SQL语句如下：

显示错误信息为：
消息 241，级别 16，状态 1，第 1 行
从字符串转换日期和/或时间时，转换失败。
消息 242，级别 16，状态 3，第 1 行
从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。

但是当SQL语句修改为下图时又可以正常转换了：




实在想不通为什么会出现这种情况，用convert转换也是同样的结果；之后我尝试着先用isnull或convert把该字段改为'1990-01-01'，然后再行转换成日期格式也不行。

PS：由于数据量较大所以不能修改字段类型，所以必须经过转换。

望各位大神支招，小弟拜谢。

[發糞塗牆]

你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

[發糞塗牆]

[Nynas]

引用 1 楼 DBA_Huangzj 的回复:

你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

嗯，表内确实找到了一些非日期格式的数据；可是我在测试的时候用order by 加top 1 固定在了一条记录上，该行的字段值为NULL，这样也会有影响吗？ 而且我刚才试验了一下，如果在where条件内指定了查询该行，就不会报错了。

[还在加载中灬]

因为你的数据中，有不能转化为DATETIME的数据 而TOP 1的查询刚好不包含此类数据，所以TOP 1 的时候可以 你可以新加一列时间列，用UPDATE TB SET ... WHERE ISDATE(时间列)=1把能转换的先转换了 然后，再看看，不能转的，是要统一转成什么，或看情况手动更新

[Nynas]

引用 1 楼 DBA_Huangzj 的回复:

你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

还有另外一个字段类型是char(8)，记录的内容为员工的生日，里面的值都是日期格式和NULL。 也同样出现了这个问题

[發糞塗牆]

drop table test 
create table test (d varchar(30))
insert into test values('2015-02-31')
insert into test values('2015-02-28')



select * from test
select cast(d as datetime)  from test where isdate(d)=1

/*
d
------------------------------
2015-02-31
2015-02-28

(2 row(s) affected)


-----------------------
2015-02-28 00:00:00.000
*/

[Nynas]

引用 4 楼 ky_min 的回复:

因为你的数据中，有不能转化为DATETIME的数据 而TOP 1的查询刚好不包含此类数据，所以TOP 1 的时候可以 你可以新加一列时间列，用UPDATE TB SET ... WHERE ISDATE(时间列)=1把能转换的先转换了 然后，再看看，不能转的，是要统一转成什么，或看情况手动更新

引用 6 楼 DBA_Huangzj 的回复:

drop table test 
create table test (d varchar(30))
insert into test values('2015-02-31')
insert into test values('2015-02-28')



select * from test
select cast(d as datetime)  from test where isdate(d)=1

/*
d
------------------------------
2015-02-31
2015-02-28

(2 row(s) affected)


-----------------------
2015-02-28 00:00:00.000
*/

查出一个问题，是因为datetime的范围只到1753年，而数据内有小于1753年的数据，所以会造成超出范围的错误。 另一个原因是该字段有脏数据，非日期格式的值。 非常感谢两位的帮助！