--查询
--按日期部分的字符排序
select * from t
order by left(riqi,len(riqi)-charindex('-',reverse(riqi)))--将日期部分转换成日期排序
select * from t
order by cast(left(riqi,len(riqi)-charindex('-',reverse(riqi))) as datetime)
--按日期部分的字符排序
select * from t
order by left(riqi,len(riqi)-charindex('-',reverse(riqi)))--将日期部分转换成日期排序
select * from t
order by cast(left(riqi,len(riqi)-charindex('-',reverse(riqi))) as datetime)
create table t(riqi Varchar(20))
insert t select '1982-6-1-1'
union all select '1982-4-1-1'
union all select '1982-4-1-1'
union all select '1981-7-1-1'
union all select '1987-1-1-1'
union all select '1981-5-1-1'
union all select '1981-5-1-1'
union all select '1981-3-1-6'
union all select '1981-1-5-5'
union all select '1981-1-4-4'
union all select '1981-1-3-3'
union all select '1981-1-31-2'
union all select '1981-1-2-1'
go--查询
--按日期部分的字符排序
select * from t
order by left(riqi,len(riqi)-charindex('-',reverse(riqi)))--将日期部分转换成日期排序
select * from t
order by cast(left(riqi,len(riqi)-charindex('-',reverse(riqi))) as datetime)
go--删除测试
drop table t/*--测试结果riqi
--------------------
1981-1-2-1
1981-1-3-3
1981-1-31-2
1981-1-4-4
1981-1-5-5
1981-3-1-6
1981-5-1-1
1981-5-1-1
1981-7-1-1
1982-4-1-1
1982-4-1-1
1982-6-1-1
1987-1-1-1(所影响的行数为 13 行)riqi
--------------------
1981-1-2-1
1981-1-3-3
1981-1-4-4
1981-1-5-5
1981-1-31-2
1981-3-1-6
1981-5-1-1
1981-5-1-1
1981-7-1-1
1982-4-1-1
1982-4-1-1
1982-6-1-1
1987-1-1-1(所影响的行数为 13 行)
--*/
order by left(riqi,8)