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,各位大虾给指点下问题出在哪里?谢谢了
from cases
where date between '2010=01-01' and getdate()
group by convert(varchar(7),date,120)
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)
if object_id('table1') is not null
drop table table1
gocreate table table1
(recordid int,recorddate datetime)
goinsert 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 table1select count(*) 数量,datepart(mm,recorddate) 月份 from table1 group by datepart(mm,recorddate)