group by 小问题 sql2000，sql2005

[a260881071]

表里有时间字段。。

我现在要按日，按月做统计。

时间字段为returntime,被统计字段为mileage

----按日
select substring(convert(varchar,returntime ,120 ),1,10),
(max(Mileage)-min(Mileage)) as MileageSpan
from vehicletrack336
where Mileage>0 group by substring(convert(varchar,returntime ,120 ),1,10)


----按月
select (substring(convert(varchar,returntime ,120 ),1,7)+ '-01') as colDate,
(max(Mileage)-min(Mileage)) as MileageSpan
from vehicletrack336
where Mileage>0 group by substring(convert(varchar,returntime ,120 ),1,7)


这里按月统计的时候在sql2000中会报错(2005中不会),错误在'-01',去掉它就没问题.
现在我统计完成之后还要查询,想把colDate转为datetime,所以在后面加了'-01'现在在sql2000中跑不了,


我应该怎么办.




--------------------------------------帅签分割线-------------------------------------------------

[GoAwayZ]

select cast((convert(varchar(7),returntime,120)+'-01') as datetime),
   (max(Mileage)-min(Mileage)) as MileageSpan
from vehicletrack336
where Mileage>0 group by cast((convert(varchar(7),returntime,120)+'-01') as datetime)

[xuexiaodong2009]

再加一次转换，楼上的方法可以