检索时间能不能只看年月？

[freshrabbit]

我的表里记录的时间都是类似'2003-12-12'这种，年月日都有的。现在我想检索2003年12月份（不管哪一天）的数据，应该怎么办呢？我试过用select * from table1 where 'convert(char(8),mydate,120)' = '2003-12'，不行。用select * from table1 where mydate like '2003-12%'也不行。

[leeboyan]

select * from table1 where mydate >='2003-12-1' and mydate <='2003-12-31'

[zjcxc]

select * from 表 where mydate between '2003-12-1' and '2003-12-31'

--或:
select * from table1 where convert(char(7),mydate,120) = '2003-12'

--或:
select * from table1 where year(mydate)=2003 and month(mydate)=12

[dcq]

select * from table where datepart(mm,mydate)=12

[ghosthjt]

这种问题也我也遇到过，解决的办法是只要年和月对上了，日子设定为-01好了。
declare @date datetime
set @date='2003-11-23'
set @date=cast(cast(year(@date) as varchar(4)) +cast(Month(@date) as varchar(2)+'-01') as datetime)

select * from table where datediff(m,mydate,@date)=0

[freshrabbit]

郁闷死！我开始就是象letsflytogether那样写的呀！为什么不对呢？又查了半天，原来不小心多写了个括号！

[伍子V5]

try:
select * from table1 where convert(char(7),mydate,120) = '2003-12'

[Groundsel]

select * from table1 where datediff(M,mydate,'2003-12-28') = 0

[Relta]

select * from table1 where convert(char(7),mydate,126) = '2003-12'