create table test
(
name varchar(10),
rq datetime)
insert into test select 'a1','2006-1-1'
insert into test select 'a2','2006-1-2'
insert into test select 'a2','2006-1-3'insert into test select 'b1','2006-2-1'
insert into test select 'b2','2006-2-2'
insert into test select 'b3','2006-2-3'insert into test select 'c1','2006-3-1'
insert into test select 'c2','2006-3-2'
insert into test select 'c3','2006-3-3'drop table test/*我想仅仅查询出1个月的数据该怎样写呢,例如:只查询出2006-1月的数据*/
(
name varchar(10),
rq datetime)
insert into test select 'a1','2006-1-1'
insert into test select 'a2','2006-1-2'
insert into test select 'a2','2006-1-3'insert into test select 'b1','2006-2-1'
insert into test select 'b2','2006-2-2'
insert into test select 'b3','2006-2-3'insert into test select 'c1','2006-3-1'
insert into test select 'c2','2006-3-2'
insert into test select 'c3','2006-3-3'drop table test/*我想仅仅查询出1个月的数据该怎样写呢,例如:只查询出2006-1月的数据*/
SELECT * FROM TEST WHERE CONVERT(VARCHAR(10),RQ,120) LIKE '2006-01%'--2.
SELECT * FROM TEST WHERE DATEDIFF(MONTH,RQ,'2006-1-1')=0
and month(rq)=1
(
name varchar(10),
rq datetime)
insert into test select 'a1','2006-1-1'
insert into test select 'a2','2006-1-2'
insert into test select 'a2','2006-1-3'insert into test select 'b1','2006-2-1'
insert into test select 'b2','2006-2-2'
insert into test select 'b3','2006-2-3'insert into test select 'c1','2006-3-1'
insert into test select 'c2','2006-3-2'
insert into test select 'c3','2006-3-3'select * from test where datepart(month,rq)='1' and datepart(year,rq)='2006'drop table test___________________________-
name rq
a1 2006-01-01 00:00:00.000
a2 2006-01-02 00:00:00.000
a2 2006-01-03 00:00:00.000
where rq >'2006-01-01' and rq < '2006-02-01'