string转换成date型

[luyisir2]

数据库中有一字段是string型，存储的是日期，比如2005年9月，存储的就是SEP-2005。现取数据时欲按这个字段排序，请教sql语句中如何把这个字段转换成date型以便于排序！

[vivianfdlpw]

declare @str varchar(10)
set @str='SEP-2005'
select right(@str,4)
+'-'
+(case left(@str,charindex('-',@str)-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'
else '12'
end
)
+'-01'

[wgsasd311]

SET LANGUAGE us_english
declare @aa datetime
set @aa=cast('01-sep-2005' as datetime)
SET LANGUAGE N'简体中文'
print @aa

[wgsasd311]

set dateformat dmy
SET LANGUAGE us_english
declare @aa datetime
set @aa=cast('01-'+'sep-2005' as datetime)
SET LANGUAGE N'简体中文'
print @aa

[luyisir2]

上面两位大虾似乎只对特定的数据进行格式化，现在的需求是按照这个字段(假如叫做PERIOD)来排序，比如：
select * from TABLE where *=* order by PERIOD
由于PERIOD是string型的，这样排序不能达到想要的结果，我想问的是能否对上面这个sql语句做简单的变化，使达到想要的结果，比如：
select * from TABLE where *=* order by toDate(PERIOD)

[luyisir2]

up

[vivianfdlpw]

create function f_convert(@str)
returns datetime
as
begin
declare @tmp varchar(20)
set @tmp=right(@str,4)
+'-'
+(case left(@str,charindex('-',@str)-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'
else '12'
end
)
+'-01'

return @tmp
end
go

--查询
select * from TABLE where *=* order by dbo.f_convert(PERIOD)

[feitianbianfu16]

select * from [TABLE] order by cast(period as datetime)

[老本]

最方便的方法是，创建一个视图。

create view Vtest
as
select right(old_date_str,4)
+'-'
+(case left(old_date_str,charindex('-',lower(@str))-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'
else '12'
end
) date_str_YYYY_MM,
old_date_str,
OtherFieldsList
from YourTable

select * from Vtest2005-9-20 20:39
order by date_str_YYYY_MM