--1.
select * from tablename where year(colname)=2011 and month(colname)=12
--2.
select * from tablename where convert(varchar(7),colname,120)='2011-12'

------解决方案--------------------
SQL code
select * from tablename where datepart（yy,colname）=2011 and datepart(mm,colname)=12

------解决方案--------------------
探讨

比如有个[过磅表]，表里有个[过磅时间]字段，时间格式是有年月日，和时分秒。
如“2011-12-12 12:23:21”这样的格式。

那我要查询某一天的过磅记录，该怎么查，字段[过磅时间]我已经加了索引，要考虑使用索引来查，优化效率。

比如查 2011-12-12日的记录，或者查 2011-12月份的记录，或者查某个时间段的记录，
该怎么写sql语句，


------解决方案--------------------
--查某天.
select * from 过磅表 where datediff(dd,过磅时间,'2011-12-12') = 0
select * from 过磅表 where convert(varchar(10),过磅时间,120) = '2011-12-12'

--查某月
select * from 过磅表 where datediff(mm,过磅时间,'2011-12-01') = 0
select * from 过磅表 where convert(varchar(7),过磅时间,120) = '2011-12'


------解决方案--------------------

2012-12-12 索引可以使用  
  select * from tablename where colname between '2012-12-12' and '2012-12-13'
2012-12 索引可以使用  
  select * from tablename where colname between '2012-12-01' and '2013-01-01'
'2012-12-12' 默认时间比较 '2012-12-12 00:00:00'



------解决方案--------------------
-- 按日期查
declare @time varchar(20)
set @time = '2011-09-16'
declare @now datetime
declare @start datetime
declare @end datetime
select @now = convert(datetime,@time)
set @start =dateadd(day,-1,@now)
set @end = dateadd(day,1,@now)
select * from reports where createTime> @start and createTime <@end

--按月份查
declare @time varchar(20)
set @time = '2011-09'
declare @start datetime
select @start =convert(datetime,@time+'-01')
declare @end datetime
set @end = dateAdd(month,1,@start)


------解决方案--------------------
-- 按日期查
declare @time varchar(20)
set @time = '2011-09-16'
declare @now datetime
declare @start datetime
declare @end datetime
select @now = convert(datetime,@time)
set @start =dateadd(day,-1,@now)
set @end = dateadd(day,1,@now)
select * from reports where createTime> @start and createTime <@end

--按月份查
declare @time varchar(20)
set @time = '2011-09'
declare @start datetime
select @start =convert(datetime,@time+'-01')
declare @end datetime
set @end = dateAdd(month,1,@start)

-- http://www.ia00.com

------解决方案--------------------
SQL code
SELECT * FROM TAB
WHERE 过磅时间 >= '2011-12-12'
AND 过磅时间 < '2011-12-13'

------解决方案--------------------
colname between '2012-12-12' and '2012-12-13 23：59：59：999'


------解决方案--------------------
探讨

colname between '2012-12-12' and '2012-12-13 23：59：59：999'


------解决方案--------------------
12楼基本正确了？，只是没处理好>=


SQL code
-- 按日期查
declare @time varchar(20)
set @time = '2011-09-16'
declare @now datetime
declare @start datetime
declare @end datetime
select @now = convert(datetime,@time)
set @start = CONVERT(VARCHAR(10),@now,120)
set @end = dateadd(day,1,@start)
select * from reports where createTime >= @start and createTime <@end

--按月份查