数据导入中日期处理问题

利用dts做数据导入，源文件为以“|”分隔的文本文件，其中每一行数据类似如下：
1|XXX|1|25-Jun-61|06-Sep-09|06-Sep-09|06-Sep-94|1|26-Sep-06|06-Sep-12|0|A

导入后发现如下问题：25-Jun-61这样的字符串导入到datetime类型字段里能正确转为1961-06-25;但是06-Sep-08和26-Sep-06都分别转换为2006-09-08和2026-09-06,如何解决导入时把日期的字符串最后2位年份转为4位年份导入，而不是有时对有时错。

那位大侠有号方法，但方法中不要改动源文件，比如把源文件中的字符先改为4位年份再导入，这不好使，因为源文件有几万条记录，太难改动。

谢谢！

11 个解决方案

#1

--字符转换为日期时,Style的使用

--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',101)
--结果:2003-11-01 00:00:00.000

--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
SELECT CONVERT(datetime,'11/1/2003',103)
--结果:2003-01-11 00:00:00.000

#2

select convert(datetime ,'06-09-2008',103)
-----------------------
2008-09-06 00:00:00.000

#3

select convert(datetime ,'06-09-08',3)
select convert(datetime ,'26-09-06',3)
/*
-----------------------
2008-09-06 00:00:00.000
-----------------------
2006-09-26 00:00:00.000
*/

#4

select   CONVERT(varchar,   getdate(),   120   )  
  2004-09-12   11:06:08  
   
  select   replace(replace(replace(CONVERT(varchar,   getdate(),   120   ),'-',''),'   ',''),':','')  
  20040912110608  
   
  select   CONVERT(varchar(12)   ,   getdate(),   111   )  
  2004/09/12  
   
  select   CONVERT(varchar(12)   ,   getdate(),   112   )  
  20040912  
   
  select   CONVERT(varchar(12)   ,   getdate(),   102   )  
  2004.09.12  
   
  其它我不常用的日期格式转换方法:  
   
  select   CONVERT(varchar(12)   ,   getdate(),   101   )  
  09/12/2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   103   )  
  12/09/2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   104   )  
  12.09.2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   105   )  
  12-09-2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   106   )  
  12   09   2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   107   )  
  09   12,   2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   108   )  
  11:06:08  
   
  select   CONVERT(varchar(12)   ,   getdate(),   109   )  
  09   12   2004   1  
   
  select   CONVERT(varchar(12)   ,   getdate(),   110   )  
  09-12-2004  
   
  select   CONVERT(varchar(12)   ,   getdate(),   113   )  
  12   09   2004   1  
   
  select   CONVERT(varchar(12)   ,   getdate(),   114   )  
  11:06:08.177   

#5

Select     
CONVERT(varchar, getdate(), 1),--mm/dd/yy    
CONVERT(varchar, getdate(), 2),--yy.mm.dd     
CONVERT(varchar, getdate(), 3),--dd/mm/yy     
CONVERT(varchar, getdate(), 4),--dd.mm.yy     
CONVERT(varchar, getdate(), 5),--dd-mm-yy     
CONVERT(varchar, getdate(), 10),--mm-dd-yy     
CONVERT(varchar, getdate(), 11),--yy/mm/dd         
CONVERT(varchar, getdate(), 12),--yymmdd
----带世纪号 
select convert(varchar(10),getdate(),100) --06 15 2007 或0
select convert(varchar(10),getdate(),102) --2007.06.15
select convert(varchar(10),getdate(),103) --15/06/2007
select convert(varchar(10),getdate(),104) --15.06.2007s
elect convert(varchar(10),getdate(),105) --15-06-2007
select convert(varchar(10),getdate(),106) --15 06 2007
select convert(varchar(10),getdate(),107) --06-15,200
select convert(varchar(10),getdate(),108) --10:06:46 当前时间
select convert(varchar(10),getdate(),109) --06 15 2007 或者9
select convert(varchar(10),getdate(),110) --06-15-2007
select convert(varchar(10),getdate(),111) --2007/06/15
select convert(varchar(10),getdate(),112) --20070615
select convert(varchar(10),getdate(),113) --15 06 2007 或者13
select convert(varchar(10),getdate(),114) --10:10:37:0
select convert(varchar(10),getdate(),120) --2007-06-15 或20
select convert(varchar(30),getdate(),121) --2007-06-15 10:11:45.040 或21
select convert(varchar(30),getdate(),126) --2007-06-15T10:12:44:603
select convert(varchar(50),getdate(),127) --2007-06-15T10:14:35:433 说带时区
select convert(varchar(40),getdate(),130) --回历dd mon yyyy hh:mi:ss:mmmAM
select convert(varchar(30),getdate(),131) --30/05/1428 10:17:19:470AM

GO

#6

另外提醒下楼主 如果你的格式一定要
26-Sep-06 
这里的SEP 月份的表示 你可以建个对应表 对应换成数字 再用上面方法处理

#7

--datetime类型字段改为字符型，导入后再update
UPDATE TB
  SET COL= CONVERT(VARCHAR(10), CAST(COL AS DATETIME), 120)

#8

楼主先用查找替换吧月份替换成数字，然后在转换

#9

可以先直接转换，转完之后写一个存储过程统一处理一下，例如日期大于当前的，减100年

#10

create table tmp_aaa(id int , type_id  int,fn int,  name  varchar(50),dTime datetime)
delete tmp_aaa
insert into tmp_aaa
select 1  ,    13 ,   1 , '你好' ,getdate() union all 
select 2  ,    75 ,   2 , '你来' ,getdate()   union all 
select 3   ,   0  ,  3 , '自哪个' ,getdate()   union all 
select 4   ,   0  ,  4 , '国家?'  ,getdate()  union all 
select 5   ,   90 ,   5,  '你吃' ,'2006-05-08'  union all 
select 6   ,   0  ,  6 , '饭了吗?','2008-05-08'  

go
EXEC   master..xp_cmdshell  
'bcp   "SELECT  *, convert(char(10),dTime,120)   FROM  test..tmp_aaa "   queryout   d:\1.txt    -c  -t"|"   -S"192.168.0.10"   -U"sa"   -P"sa"' 
--测试成功！
============================
1.txt 内容为
1|13|1|你好|2009-07-28 14:04:15.080|2009-07-28
2|75|2|你来|2009-07-28 14:04:15.080|2009-07-28
3|0|3|自哪个|2009-07-28 14:04:15.080|2009-07-28
4|0|4|国家?|2009-07-28 14:04:15.080|2009-07-28
5|90|5|你吃|2006-05-08 00:00:00.000|2006-05-08
6|0|6|饭了吗?|2008-05-08 00:00:00.000|2008-05-08

#11

EXEC   master..xp_cmdshell  
'bcp   "SELECT  *, convert(char(10),dTime,120)   FROM  test..tmp_aaa "   queryout  
d:\1.txt    -c  -t"|"   -S"192.168.0.10"   -U"sa"   -P"sa"'

--对着改改，就行！