表中有一个字段 CDKey--varchar(255),
其数据类似于如下:
A080515S0010075
A080413S0670032
A080222S0150033
A080510S3310006
...............以A080515S0010075为例,用substring(CDKey,2,6)得到的就是此CDKey的日期,为080515.想得到的结果是:将此080515转化为日期格式,然后再与当前日期比较,得出二者的日期差.此处比较麻烦的是如何将080515转化为日期格式.
datediff(d,convert(datetime,cast(substring('20'+ substring(CDKey,2,6),1,4)+ '-' + substring('20'+ substring(CDKey,2,6),5,2) + '-' + substring('20'+ substring(CDKey,2,6),7,2) as Datetime),1),getdate()) as '时间差'但是这样执行的时候,一直提示"从字符串向 datetime 转换时失败。"请指点思路,谢谢.
其数据类似于如下:
A080515S0010075
A080413S0670032
A080222S0150033
A080510S3310006
...............以A080515S0010075为例,用substring(CDKey,2,6)得到的就是此CDKey的日期,为080515.想得到的结果是:将此080515转化为日期格式,然后再与当前日期比较,得出二者的日期差.此处比较麻烦的是如何将080515转化为日期格式.
datediff(d,convert(datetime,cast(substring('20'+ substring(CDKey,2,6),1,4)+ '-' + substring('20'+ substring(CDKey,2,6),5,2) + '-' + substring('20'+ substring(CDKey,2,6),7,2) as Datetime),1),getdate()) as '时间差'但是这样执行的时候,一直提示"从字符串向 datetime 转换时失败。"请指点思路,谢谢.
declare @tb table([dt] nvarchar(15))
Insert @tb
select N'A080515S0010075' union all
select N'A080413S0670032' union all
select N'A080222S0150033' union all
select N'A080510S3310006'
Select datediff(day,cast('20'+ substring([dt],2,6)as datetime),getdate()) as diff from @tb
/*
diff
-----------
51
83
134
56
*/
insert #1 select 'A080515S0010075'
insert #1 select 'A080413S0670032'
insert #1 select 'A080222S0150033'
insert #1 select 'A080510S3310006'
insert #1 select 'A080705S3310006' select datediff(d,'20'+substring(a,2,6),getdate()) from #1
---------
51
83
134
56
0
insert #1 select 'A080515S0010075'
insert #1 select 'A080413S0670032'
insert #1 select 'A080222S0150033'
insert #1 select 'A080510S3310006'
insert #1 select 'A080705S3310006' select datediff(d,'20'+substring(a,2,6),getdate()) from #1
---------
51
83
134
56
0
是网络问题还是csdn的问题?咋这慢呢