sql server 字符串处理和数据类型转换几点体会

昨天同事给我一个Excel文件,需要分析其中的数据,具体格式如下:

运单单号

提车票号

分票日期

出库日期

限达日期

实达日期

限返日期

实返日期

商品系列

车型编码

颜色

底盘号

出库单号

备注

31108180

202761

2007/09/26

2007-9-26

2007/09/26

2007/09/26

2007/09/26

2007/09/26

OU07092500325

31091340

200682

2007/09/18

2007-9-18

2007/09/18

2007/09/18

2007/09/18

2007/09/18

OU07091700355

31158609

205546

2007/10/25

2007-10-25

2007/10/29

2007/10/27

2007/11/03

2007/11/03

OU07101700140

30424488

149683

2007/01/09

2007-1-9

2007/01/12

2007/01/12

2007/01/17

2007/01/17

OU07010400236

30429750

149678

2007/01/13

2007-1-13

2007/01/16

2007/01/15

2007/01/22

2007/01/17

OU07010400236

30429750

149680

2007/01/13

2007-1-13

2007/01/16

2007/01/15

2007/01/22

2007/01/17

OU07010400236

将Excel文件导入到sql server 中,发现其中的限达日期等日期类型都识别成了nvarchar(255)类型,无法使用时间函数进行数据分析,如果强制性的在表设计器中更改数据类型为datetime就会报错,无法继续:

继续网上搜索,大部分的解决方案是用convert 和cast函数,转换,就直接测试用如下语句转换:

select convert(datetime,限达日期) from wkck

用

select cast(限达日期 as datetime) from wkck 都报如下的错误:

服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。
难道是语法错误,或者是网上的说明有误,仔细查看convert 和cast函数的使用说明,可以从nvarchar类型转换为datetime类型,这就引起了我的警觉,看来是输入的数据有问题:

为了验证我的判断,我用如下语句测试一下:

select left(限达日期,10) from wkck

返回数据是如下格式:

 2007/09/2

不对啊,导入的数据日期格式是"2007/01/01",一共是10个字符,怎么最后一个字符没有显示?

这个测试基本证实了我的判断,在导入的日期字符串的开头有一个空格,导致convert或者cast函数执行的时候因为字符不符合datetime的规则而报错,知道了这个原因就好办了,先用

select LTRIM(实达日期) FROM wkck 等语句删除空格,发现字符的格式没有变,开头还是有空格,先后测试了left,right等字符串函数,最后用sunbstring 函数解决问题,如下:

SELECT SUBSTRING(实返日期,2,12) FROM wkck

结果:2007/09/26

空格去掉,执行转换:

SELECT convert(datetime,SUBSTRING(实返日期,2,12)) FROM wkck

结果:2007-09-26 00:00:00.000

OK!完成任务

本次处理的体会:

1. 在做字符串处理之前一定到好好分析一下自己的数据:
2. 应用程序的在输入数据时要加强控制,避免空格的输入:
3. 遇到问题,多测试比较,肯定能有所收获

本次问题消耗我一天的时间,惭愧!惭愧!希望增长这方面的经验和教训,以后少在此方面走弯路!