有关日期问题的sql
数据库中有两个字段:appDate,money
值如下:
appDate money
2003-10-22 00:00:00 400
2003-10-22 12:30:00 600
2003-10-23 00:00:00 2000
现在报表要求取这样的结果:
Date weekday money
----------- -------- -------
2003-10-22 星期三 1000
2003-10-23 星期四 2000
用这样的语句取出来的日期值不能合并;
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by appDate
而用这样的语句就报错
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by Convert(Char(10),appDate,20)
麻烦大家帮忙看看怎么解决这个问题,或者用其他方式取出报表要求的结果