按时间（年，月，季度，周）查询统计

准备案例数据：

 

--SQL code--建表
create table #Temp (date datetime, Dcount int)
insert into #Temp
select '2010-01-01',10 union all
select '2010-01-25',10 union all
select '2010-01-31',15 union all
select '2010-02-25',30 union all
select '2010-02-28',20 union all
select '2011-01-03',20 union all
select '2010-03-01 12:11:4',10 union all
select '2010-04-25 13:11:4',10 union all
select '2010-05-31 14:11:4',15 union all
select '2010-06-25 15:11:4',30 union all
select '2010-07-28 16:11:4',20 union all
select '2011-08-03 17:17:4',20 union all
select '2010-10-25 18:11:4',30 union all
select '2010-11-28 19:11:4',20 union all
select '2011-12-03 20:11:4',20 union all
select '2011-09-18 21:11:4',28 

例题如下：

 

--1 按每年的月统计总数量
select datepart(year,date) as '年',datepart(month,date) '月' ,sum(Dcount) '总数' from #Temp
group by datepart(year,date),datepart(month,date)
order by datepart(year,date),datepart(month,date)
--2 查询2010-11-28 日数量 

select dcount from #temp 
where convert(char(10),date,120)='2010-11-28'

select dcount from #temp 
where date between '2010-11-28' and '2010-11-29'
--结果 方法2比方法1效率高些
--3查询本月的统计 
select * from #temp where DATEPART(mm, Date)
=DATEPART(mm, GETDATE()) and DATEPART(yy, Date) 
= DATEPART(yy, GETDATE())
--11.查询本周的记录

select * from #temp where DATEPART(wk, Date) = DATEPART
(wk, GETDATE()) and DATEPART(yy, Date) = DATEPART(yy, GETDATE())

--11.查询本季度的记录
select * from #temp where DATEPART(qq, Date) = DATEPART
(qq, GETDATE()) and DATEPART(yy, Date) = DATEPART(yy, GETDATE())
--时间与字符串的转化
select convert(datetime,'2010-11-28')--2010-11-28 00:00:00.000
select convert(datetime,'2010-11-28 12:1:2') --2010-11-28 12:01:02.000