sql 如何取日期中的年月

[左手青春右手年华]

year(time)+'-'+month(time) 虽然可以实现，但是无法按此拼接的时间格式进行正常升降序排列
select (cast(year(time)+'-'+month(time) as datetime)) as d,sum(num) as c from table group by d,c order by d desc
错误：（1）不能在聚合查询用别名字段；（2）这种将yyyy-mm的字符串转化成时间格式报错
请高手指点

[江南小鱼]

select cast(year(time)+'-'+month(time) as datetime) as d,sum(num) as c from table group by cast(year(time)+'-'+month(time) as datetime) order by cast(year(time)+'-'+month(time) as datetime) desc

[Dogfish]

楼主order by的时候可以用order by year(time), month(time) 效果一样的。

[allen0118]

select convert(nvarchar(6),getdate(),112)

[秋的红果实]

年-月，也能转换为日期类型？你需要加上日，才能转

[秋的红果实]

等你把d转为有效的时间格式，排序就可以这样：order by convert(datetime,d) DESC，这样就让最新数据排到了上面，达到你要的效果

[大葡萄八块一斤]

当然不能group by 是分组，没分好数据代表还没有你的别名数据，所以用不了，而在order by 就可以用前面的别名了

SELECT CONVERT(VARCHAR(7),time,23) AS d,SUM(num) AS c FROM table GROUP BY CONVERT(VARCHAR(7),time,23)
ORDER BY d DESC