--本年 select m as [Date],sum(case when datepart(month,CreateDateTime)=m then 1 else 0 end )as [Count] from [dbo].CrmCustomer c, (select 1 m union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) aa where '2018'= year(CreateDateTime) group by m; --本月 select DATEPART(day,CreateDateTime) as [Date],COUNT(id) AS [Count] from [dbo].CrmCustomer WHERE DATEPART(mm, CreateDateTime) = DATEPART(mm, GETDATE()) and DATEPART(yy, CreateDateTime) = DATEPART(yy, GETDATE()) group by DATEPART(day,CreateDateTime)
--本周 select convert(nvarchar(10),CreateDateTime,120) AS [Date],COUNT(id) as [Count] from [dbo].CrmCustomer where datediff(week, CreateDateTime ,getdate())=0 group by convert(nvarchar(10),CreateDateTime,120)