字符串转成日期

[以梦为马-不负韶华]

把这样的字符串 2010 01 07 08 01 40

转换成日期格式

2010-01-07 08:01:40





？？？？？？？？？

[以梦为马-不负韶华]

大家帮我看看我的代码

if exists (select * from sysobjects where name='p_time')
drop procedure p_time
GO
create proc p_time
as
declare @riqi varchar(50)
declare @shijian varchar(50)
declare @uid varchar(50)
declare myCursor cursor for
select [user_Id],fillDate from cj_yonghu where isDate(fillDate)=0
open myCursor
fetch next from myCursor into @uid,@shijian
while @@fetch_status=0
begin
set @riqi=convert(datetime,substring(left(@shijian,8)+' ' + substring(@shijian,9,2)+':' + substring(@shijian,11,2)+':' + substring(@shijian,13,2),1,20))
update cj_yonghu set fillDate=@riqi
where [user_Id]=@uid
fetch next from myCursor into @uid,@shijian
end
close myCursor
deallocate myCursor
--print @uid
print @shijian
print @riqi
GO

exec p_time

执行结果：
20100107080140
01 7 2010 8:01AM 这个格式不对呀！
我要转换成 2010-01-07 08:01:40.000

帮我看看怎么修改？？

[cs_lb]

这样改

if exists (select * from sysobjects where name='p_time')
drop procedure p_time
GO
create proc p_time
as
declare @riqi varchar(50)
declare @shijian varchar(50)
declare @uid varchar(50)
declare myCursor cursor for
select [user_Id],fillDate from cj_yonghu where isDate(fillDate)=0
open myCursor
fetch next from myCursor into @uid,@shijian
while @@fetch_status=0
begin
set @riqi=CONVERT(varchar(50),convert(datetime,substring(left(@shijian,8)+' ' + substring(@shijian,9,2)+':' + substring(@shijian,11,2)+':' + substring(@shijian,13,2),1,20)),109)  
update cj_yonghu set fillDate=@riqi
where [user_Id]=@uid
fetch next from myCursor into @uid,@shijian
end
close myCursor
deallocate myCursor
--print @uid  
print @shijian
print @riqi
GO

exec p_time

[cs_lb]

if exists (select * from sysobjects where name='p_time')
drop procedure p_time
GO
create proc p_time
as
declare @riqi varchar(50)
declare @shijian varchar(50)
declare @uid varchar(50)
declare myCursor cursor for
select [user_Id],fillDate from cj_yonghu where isDate(fillDate)=0
open myCursor
fetch next from myCursor into @uid,@shijian
while @@fetch_status=0
begin
set @riqi=CONVERT(varchar(50),convert(datetime,substring(left(@shijian,8)+' ' + substring(@shijian,9,2)+':' + substring(@shijian,11,2)+':' + substring(@shijian,13,2),1,20)),120) --120为你想要的格式，109为你的默认格式 
update cj_yonghu set fillDate=@riqi
where [user_Id]=@uid
fetch next from myCursor into @uid,@shijian
end
close myCursor
deallocate myCursor
--print @uid  
print @shijian
print @riqi
GO

exec p_time

[水族杰纶]

select stuff(stuff(stuff(stuff('2010 01 07 08 01 40',5,1,'-'),8,1,'-'),14,1,':'),17,1,':')

[水族杰纶]

/*
---------------------
2010-01-07 08:01:40

(1 row(s) affected)
*/

[WaterGG]

declare @d varchar(50)
set @d='2010 01 07 08 01 40'

select stuff(stuff(stuff(replace(@d,' ','-'),11,1,' '),14,1,':'),17,1,':')


2010-01-07 08:01:40

(1 行受影响)

[fpzgm]

declare @str nvarchar(100)
set @str='2010 01 07 08 01 40'
select cast(replace(left(@str,10),' ','-')+' '+replace(RIGHT(@str,8),' ',':') as datetime)

/*
2010-01-07 08:01:40.000
*/

[cs_lb]

select CAST(stuff(stuff('2010 01 07 08 01 40',14,1,':'),17,1,':')AS DATETIME)

[soulJava]

select convert(datetime,replace(substring('2010 01 07 08 01 40',0,11),' ','-')+' '+replace(substring('2010 01 07 08 01 40',12,20),' ',':'),120)

[以梦为马-不负韶华]

O(∩_∩)O谢谢大家