declare @t varchar(50),@month varchar(20) select @t='09/June/2005:17:09:10' select @t=stuff(@t,charindex(':',@t),1,' ') --获取月份字母 select @month=substring(@t,charindex('/',@t)+1,len(@t)-charindex('/',reverse(@t))-charindex('/',@t)) select convert(datetime, replace(@t,@month,(case lower(@month) when 'january' then '01' when 'february' then '02' when 'march' then '03' when 'april' then '04' when 'may' then '05' when 'june' then '06' when 'july' then '07' when 'aguest' then '08' when 'september' then '09' when 'october' then '10' when 'november' then '11' when 'december' then '12' end )) ,120) /* 结果: 2005-09-06 17:09:10.000 */ --请参照实际的系统确定月份为全写,缩写等相应修改这里的语句。 --如果你的语言设置未美国时间。可以使用'May 1, 1997'这种格式。
declare @datestr varchar(40) set @datestr='09/Jun/2005:17:09:10' declare @index1 int, @index2 int declare @mm varchar(2), @dd varchar(2), @yy varchar(4), @hms varchar(8) select @index1=charindex('/', @datestr), @index2=charindex('/', @datestr, @index1+1) select @mm=(case substring(@datestr, @index1+1, @index2-@index1-1) when 'Jan' then '01' when 'Feb' then '02' when 'Mar' then '03' when 'Apr' then '04' when 'May' then '05' when 'Jun' then '06' when 'Jul' then '07' when 'Aug' then '08' when 'Sep' then '09' when 'Oct' then '10' when 'Nov' then '11' when 'Dec' then '12' else '01' end) select @dd=left(@datestr, 2) select @yy=substring(@datestr, @index2+1, 4) select @hms=right(@datestr, 8) select [year]=@yy, [month]=@mm, [day]=@dd, hms=@hms select convert(datetime, @yy+'-'+@mm+'-'+@dd+' '+@hms) ,convert(varchar(20), convert(datetime, @yy+'-'+@mm+'-'+@dd+' '+@hms), 20)
select @t='09/June/2005:17:09:10'
select @t=stuff(@t,charindex(':',@t),1,' ')
--获取月份字母
select @month=substring(@t,charindex('/',@t)+1,len(@t)-charindex('/',reverse(@t))-charindex('/',@t))
select convert(datetime,
replace(@t,@month,(case lower(@month)
when 'january' then '01'
when 'february' then '02'
when 'march' then '03'
when 'april' then '04'
when 'may' then '05'
when 'june' then '06'
when 'july' then '07'
when 'aguest' then '08'
when 'september' then '09'
when 'october' then '10'
when 'november' then '11'
when 'december' then '12'
end ))
,120)
/*
结果:
2005-09-06 17:09:10.000
*/
--请参照实际的系统确定月份为全写,缩写等相应修改这里的语句。
--如果你的语言设置未美国时间。可以使用'May 1, 1997'这种格式。
set @datestr='09/Jun/2005:17:09:10'
declare @index1 int, @index2 int
declare @mm varchar(2), @dd varchar(2), @yy varchar(4), @hms varchar(8)
select @index1=charindex('/', @datestr), @index2=charindex('/', @datestr, @index1+1)
select @mm=(case substring(@datestr, @index1+1, @index2-@index1-1)
when 'Jan' then '01' when 'Feb' then '02' when 'Mar' then '03'
when 'Apr' then '04' when 'May' then '05' when 'Jun' then '06'
when 'Jul' then '07' when 'Aug' then '08' when 'Sep' then '09'
when 'Oct' then '10' when 'Nov' then '11' when 'Dec' then '12'
else '01' end)
select @dd=left(@datestr, 2)
select @yy=substring(@datestr, @index2+1, 4)
select @hms=right(@datestr, 8)
select [year]=@yy, [month]=@mm, [day]=@dd, hms=@hms
select convert(datetime, @yy+'-'+@mm+'-'+@dd+' '+@hms)
,convert(varchar(20), convert(datetime, @yy+'-'+@mm+'-'+@dd+' '+@hms), 20)