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'
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'
declare @aa datetime
set @aa=cast('01-sep-2005' as datetime)
SET LANGUAGE N'简体中文'
print @aa
SET LANGUAGE us_english
declare @aa datetime
set @aa=cast('01-'+'sep-2005' as datetime)
SET LANGUAGE N'简体中文'
print @aa
select * from TABLE where *=* order by PERIOD
由于PERIOD是string型的,这样排序不能达到想要的结果,我想问的是能否对上面这个sql语句做简单的变化,使达到想要的结果,比如:
select * from TABLE where *=* order by toDate(PERIOD)
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)
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