--昨天注册会员数量 select count(*) from tablename where datediff(day,RegDate,getdate())=1--今日注册会员数量 select count(*) from tablename where datediff(day,RegDate,getdate())=0
--昨天注册会员数量 select count(*) from tablename where datediff(day,RegDate,getdate())=1--今日注册会员数量 select count(*) from tablename where datediff(day,RegDate,getdate())=0
select * from 表 where datediff(d,RegDate,getdate())<=1
返回跨两个指定日期的日期和时间边界数。 语法 DATEDIFF ( datepart , startdate , enddate ) Select Sum(1)As total From tb Where (Convert(varchar(10),RegDate 120)=Convert(varchar(10),Getdate(),120)) Or (DATEDIFF(day,RegDate,Getdate())=1
select count(*) from 表 where datediff(d,RegDate,getdate())<=1
Select Sum(1)As total From tb Where datediff(day,RegDate,getdate())=0 Or (DATEDIFF(day,RegDate,Getdate())=1
select date = case DATEDIFF ( dd, RegDate,GetDate()) when 0 then 'todate' else 'yesterday 'end ,count(*) from tablename where DATEDIFF ( dd, RegDate,GetDate())<2 group by case DATEDIFF ( dd, RegDate,GetDate()) when 0 then 'todate' else 'yesterday 'end
create table A(ID int identity,username varchar(20),RegDate datetime default getdate()) insert A(username,RegDate) select 'AAA','2005-1-1' union select 'BBB','2005-2-1' union select 'CCC','2005-7-19' union select 'DDD','2005-7-20 14:23:24' union select 'EEE','2005-7-21 06:00:23' union select 'GGG','2005-7-20 10:21:00' goif exists(select 1 from sysobjects where id=object_id('getcount') and xtype='P') drop procedure getcount go create procedure getcount as select [昨日数量]=sum(case when datediff(day,Regdate,dateadd(day,-1,getdate()))=0 then 1 else 0 end), [今日数量]=sum(case when datediff(day,Regdate,getdate())=0 then 1 else 0 end) from A go--测试 exec getcount--删除测试环境 drop procedure getcount drop table A--结果 /*昨日数量 今日数量 ----------- ----------- 2 1(所影响的行数为 1 行) */
--昨天注册会员数量
select count(*) from tablename where datediff(day,RegDate,getdate())=1--今日注册会员数量
select count(*) from tablename where datediff(day,RegDate,getdate())=0
语法
DATEDIFF ( datepart , startdate , enddate ) Select Sum(1)As total From tb Where (Convert(varchar(10),RegDate 120)=Convert(varchar(10),Getdate(),120)) Or (DATEDIFF(day,RegDate,Getdate())=1
group by case DATEDIFF ( dd, RegDate,GetDate()) when 0 then 'todate' else 'yesterday 'end
insert A(username,RegDate)
select 'AAA','2005-1-1' union
select 'BBB','2005-2-1' union
select 'CCC','2005-7-19' union
select 'DDD','2005-7-20 14:23:24' union
select 'EEE','2005-7-21 06:00:23' union
select 'GGG','2005-7-20 10:21:00'
goif exists(select 1 from sysobjects where id=object_id('getcount') and xtype='P')
drop procedure getcount
go
create procedure getcount
as
select [昨日数量]=sum(case when datediff(day,Regdate,dateadd(day,-1,getdate()))=0 then 1 else 0 end),
[今日数量]=sum(case when datediff(day,Regdate,getdate())=0 then 1 else 0 end)
from A
go--测试
exec getcount--删除测试环境
drop procedure getcount
drop table A--结果
/*昨日数量 今日数量
----------- -----------
2 1(所影响的行数为 1 行)
*/