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

⋅关于转换日期格式时碰到的奇葩问题 ⋅关于任务计划程序，显示完成但未运行 更多帖子 关注私信空间博客 nynas 本版专家分：0 结帖率 100%:      在SQL中有一个字段：

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

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

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




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

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

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



0 2015-01-12 11:08:52

回复数 7 只看楼主引用举报楼主

⋅發糞塗牆的留言板

⋅今天希望大家帮个忙（不吹水）

⋅SQL Server 分区问题征集贴2

更多帖子

关注私信空间博客

發糞塗牆

本版专家分：214338

版主

签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！

状元 2014年总版技术专家分年内排行榜第一

榜眼 2013年总版技术专家分年内排行榜第二

金牌 2014年8月总版技术专家分月排行榜第一
2014年7月总版技术专家分月排行榜第一
2014年6月总版技术专家分月排行榜第一
2014年5月总版技术专家分月排行榜第一
2014年4月总版技术专家分月排行榜第一
2014年3月总版技术专家分月排行榜第一
2014年1月总版技术专家分月排行榜第一
2013年12月总版技术专家分月排行榜第一: 你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

0 2015-01-12 11:13:43

只看TA 引用举报 #1 得分 0

⋅發糞塗牆的留言板

⋅今天希望大家帮个忙（不吹水）

⋅SQL Server 分区问题征集贴2

更多帖子

关注私信空间博客

發糞塗牆

本版专家分：214338

版主

签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！

状元 2014年总版技术专家分年内排行榜第一

榜眼 2013年总版技术专家分年内排行榜第二

金牌 2014年8月总版技术专家分月排行榜第一
2014年7月总版技术专家分月排行榜第一
2014年6月总版技术专家分月排行榜第一
2014年5月总版技术专家分月排行榜第一
2014年4月总版技术专家分月排行榜第一
2014年3月总版技术专家分月排行榜第一
2014年1月总版技术专家分月排行榜第一
2013年12月总版技术专家分月排行榜第一: 0 2015-01-12 11:20:19

只看TA 引用举报 #2 得分 50

⋅关于转换日期格式时碰到的奇葩问题 ⋅关于任务计划程序，显示完成但未运行 更多帖子 关注私信空间博客 nynas 本版专家分：0: 引用 1 楼 DBA_Huangzj 的回复:
你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

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

0 2015-01-12 11:24:12

只看TA 引用举报 #3 得分 0

⋅好久没来，散分~~ ⋅近半年没上论坛了，散分~~ ⋅五一，嗨皮去吧 更多帖子 关注私信空间博客 还在加载中灬 本版专家分：52320 铜牌 2015年5月总版技术专家分月排行榜第三 2015年4月总版技术专家分月排行榜第三 2014年12月总版技术专家分月排行榜第三 红花 2015年6月 MS-SQL Server大版内专家分月排行榜第一 2015年5月 MS-SQL Server大版内专家分月排行榜第一 2015年4月 MS-SQL Server大版内专家分月排行榜第一 2015年3月 MS-SQL Server大版内专家分月排行榜第一 2015年2月 MS-SQL Server大版内专家分月排行榜第一 2015年1月 MS-SQL Server大版内专家分月排行榜第一 2014年12月 MS-SQL Server大版内专家分月排行榜第一 2014年11月 MS-SQL Server大版内专家分月排行榜第一 2014年10月 MS-SQL Server大版内专家分月排行榜第一: 因为你的数据中，有不能转化为DATETIME的数据

而TOP 1的查询刚好不包含此类数据，所以TOP 1 的时候可以

你可以新加一列时间列，用UPDATE TB SET ... WHERE ISDATE(时间列)=1把能转换的先转换了

然后，再看看，不能转的，是要统一转成什么，或看情况手动更新

0 2015-01-12 11:26:18

只看TA 引用举报 #4 得分 50

⋅关于转换日期格式时碰到的奇葩问题 ⋅关于任务计划程序，显示完成但未运行 更多帖子 关注私信空间博客 nynas 本版专家分：0: 引用 1 楼 DBA_Huangzj 的回复:
你用isdate()函数检查一下是否有非日期格式的数据，了比如2月30号这样的数据

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

0 2015-01-12 11:27:28

只看TA 引用举报 #5 得分 0

⋅發糞塗牆的留言板

⋅今天希望大家帮个忙（不吹水）

⋅SQL Server 分区问题征集贴2

更多帖子

关注私信空间博客

發糞塗牆

本版专家分：214338

版主

签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！

状元 2014年总版技术专家分年内排行榜第一

榜眼 2013年总版技术专家分年内排行榜第二

金牌 2014年8月总版技术专家分月排行榜第一
2014年7月总版技术专家分月排行榜第一
2014年6月总版技术专家分月排行榜第一
2014年5月总版技术专家分月排行榜第一
2014年4月总版技术专家分月排行榜第一
2014年3月总版技术专家分月排行榜第一
2014年1月总版技术专家分月排行榜第一
2013年12月总版技术专家分月排行榜第一

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

*/

0 2015-01-12 11:28:13

只看TA 引用举报 #6 得分 0

⋅关于转换日期格式时碰到的奇葩问题 ⋅关于任务计划程序，显示完成但未运行 更多帖子 关注私信空间博客 nynas 本版专家分：0

引用 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年的数据，所以会造成超出范围的错误。
另一个原因是该字段有脏数据，非日期格式的值。

非常感谢两位的帮助！

0 2015-01-12 11:46:15

只看TA 引用举报 #7 得分 0

关于转换日期格式时碰到的奇葩问题 [问题点数：100分，结帖人Nynas]