sql sever中如何查询每个月的记录

[一个热爱bug的程序猿]

cases表中date是smalldatetime类型数据，现在我想查询2010年一月至今的每个月的记录各有多少条，注意是按月查询，sql 语句该怎么写？搞了半天也没搞正确，我是这样写的：select count(*) as 数量,convert(varchar(6),date,112) as date from cases where date between convert(varchar(6),2010-1-30,112) and convert(varchar(6),getdate(),112) group by date，各位大虾给指点下问题出在哪里？谢谢了

[一个热爱bug的程序猿]

各位大虾快来帮帮忙啊

[dawugui]

select count(*) as 数量,convert(varchar(7),date,120) as date 
from cases 
where date between '2010=01-01' and getdate()
group by convert(varchar(7),date,120)

[喜-喜]

select count(*) as 数量,convert(varchar(6),date,112) as date from cases where date between convert(varchar(6),'2010-1-30',112) and convert(varchar(6),getdate(),112) group by convert(varchar(6),date,112)

[百年树人]

select 
  count(*) as 数量,
  convert(varchar(6),date,112) as date 
from 
  cases 
where 
  convert(varchar(6),date,112) between '201001' and convert(varchar(6),getdate(),112) 
group by 
  convert(varchar(6),date,112)

[vivai2010]

if object_id('table1') is not null
drop table table1
go

create table table1
(recordid int,recorddate datetime)
go

insert into table1
select 1,'2010-01-01'
union
select 1,'2010-01-11'
union
select 1,'2010-01-21'
union
select 1,'2010-02-01'
union
select 1,'2010-02-11'
union
select 1,'2010-03-01'
union
select 1,'2010-03-21'

select * from table1

select count(*) 数量,datepart(mm,recorddate) 月份 from table1 group by datepart(mm,recorddate)

[一个热爱bug的程序猿]

呵呵呵，谢谢各位了，问题成功解决！