统计昨天、当月、上月、本周的SQL语句

统计上周的数据：

select * from tb where datediff( wk, C_DATETIME, '2010-01-25 13:43:47.000') = 1

select * from tb where datediff( wk, C_DATETIME, getdate() ) = 1

统计昨天的数据：

select * from tb where day(C_DATETIME)=(day(getdate())-1)

统计当月的数据：

select * from tb where datediff(mm,C_DATETIME,'2009-10-30 15:05:00.170')=0 --当月

select * from tb where datediff(mm,C_DATETIME,getdate())=0 --当月

统计当年的数据：

select * from tb where datediff(year,C_DATETIME,'2009-10-30 15:05:00.170')=0  --当年

select * from tb where datediff(year,C_DATETIME,getdate())=0  --当年

统计上月的数据：

select * from tb where month(C_DATETIME)=(month('2009-07-30 15:05:00.170')-1) --group by Userid  --上月的

select sum(Marks),Userid from BBSScore where month(CreateDate)=(month(getdate())-1) group by Userid

 

select * from dbo.Web_UserAccount where datediff(week,C_DATETIME,getdate())=0     --C_DATETIME为日期字段        本周：
select * from dbo.Web_UserAccount where datediff(Month,C_DATETIME,getdate())=0   --C_DATETIME为日期字段        本月：
select * from dbo.Web_UserAccount where datediff(qq,C_DATETIME,getdate())=0        --C_DATETIME为日期字段         本季：
select * from dbo.Web_UserAccount where datepart(mm,C_DATETIME)/7 = datepart(mm,getdate())/7            --前半年1-6，后半年7-12：

 

 

 

一天 "select * from T_news where datediff(day,addtime,getdate())=0";

三天 "select * from T_news where datediff(day,addtime,getdate())<= 2 and datediff(day,addtime,getdate())>= 0";
一周 "SELECT * FROM T_news WHERE (DATEPART(wk, addtime) = DATEPART(wk, GETDATE())) AND (DATEPART(yy, addtime) = DATEPART(yy, GETDATE()))";
一月"SELECT * FROM T_news WHERE (DATEPART(yy, addtime) = DATEPART(yy, GETDATE())) AND (DATEPART(mm, addtime) = DATEPART(mm, GETDATE()))";
一季度 "select * from t_news where DATEPART(qq, addtime) = DATEPART(qq, GETDATE()) and DATEPART(yy, addtime) = DATEPART(yy, GETDATE())";

其中T_news是表名，addtime是数据库中对于的时间列