select sum(no01) from Table where endtime between '2011-04-04 08:00:00' and '2011-04-05 08:00:00'
select sum(no01) from tb where endtime between convert(nvarchar(10),getdate(),120)+' 08:00:00' and convert(nvarchar(10),getdate()+1,120)+' 08:00:00'
select sum(no01) from tb where endtime between convert(varchar(10),getdate(),120)+' 08:00:00' and convert(varchar(10),dateadd(dd,1,getdate()),120)+' 08:00:00'
SELECT COUNVERT(CHAR(10),GETDATE(),21) +'08:00:00' AS BGRQ, ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ, SUM(NO01) AS ABC FROM TB WHERE ENDTIME>= COUNVERT(CHAR(10,GETDATE(),21) +'08:00:00' AND ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'
SELECT CONVERT(CHAR(10),GETDATE(),21) +'08:00:00' AS BGRQ, CONVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ, SUM(NO01) AS ABC FROM TB WHERE ENDTIME>= COUNVERT(CHAR(10,GETDATE(),21) +'08:00:00' AND ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'
SELECT CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120), sum(no01) from tb group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)
SELECT CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120), sum(no01) from tb group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)
这个用个循环,查出一天的后,一个一个的用UNION ALL连接吧
select LEFT(convert(varchar(30),dt,120),10) as d,SUM([no01]) as s from ( SELECT dateadd(hh,-8,[endtime]) as dt,[no01] --减8小时,将当天8点前的数额统计到前一天 FROM tablename ) b group by LEFT(convert(varchar(30),dt,120),10) order by d
select sum(no01) from Table where endtime between '2011-04-04 08:00:00' and '2011-04-05 08:00:00'
where endtime
between convert(nvarchar(10),getdate(),120)+' 08:00:00'
and convert(nvarchar(10),getdate()+1,120)+' 08:00:00'
sum(no01)
from
tb
where
endtime
between
convert(varchar(10),getdate(),120)+' 08:00:00'
and
convert(varchar(10),dateadd(dd,1,getdate()),120)+' 08:00:00'
SELECT COUNVERT(CHAR(10),GETDATE(),21) +'08:00:00' AS BGRQ,
ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ,
SUM(NO01) AS ABC
FROM TB
WHERE ENDTIME>= COUNVERT(CHAR(10,GETDATE(),21) +'08:00:00' AND
ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'
CONVERT(CHAR(10),GETDATE()+1,21)+'08:00:00' AS EDRQ,
SUM(NO01) AS ABC
FROM TB
WHERE ENDTIME>= COUNVERT(CHAR(10,GETDATE(),21) +'08:00:00' AND
ENDTIME<= COUNVERT(CHAR(10),GETDATE()+1,21)+'08:00:00'
可能我表述的不够清楚
我需要的数据为
2010-1-1 08:00:00 ~2010-1-2 08:00:00记为2010-1-1,然后sum(no01)
2010-1-2 08:00:00 ~2010-1-3 08:00:00记为2010-1-2,然后sum(no01)
2010-1-3 08:00:00 ~2010-1-4 08:00:00记为2010-1-3,然后sum(no01)
sum(no01) from tb
group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)
sum(no01) from tb
group by CONVERT(varchar(10), DATEADD(HH, -8, endtime ), 120)
select LEFT(convert(varchar(30),dt,120),10) as d,SUM([no01]) as s from
(
SELECT dateadd(hh,-8,[endtime]) as dt,[no01] --减8小时,将当天8点前的数额统计到前一天
FROM tablename
) b group by LEFT(convert(varchar(30),dt,120),10)
order by d