求一条SQL语句，查询出本年本月的所有数据，这样的语句怎么写？

[海里猿人]

求一条SQL语句，查询出本年本月的所有数据，这样的语句怎么写？

[liangCK]

SELECT *
FROM tb
WHERE 日期 >= DATEADD(day,-DATEPART(day,GETDATE())+1,GETDATE())
    AND 日期 < DATEADD(month,1,CONVERT(VARCHAR(7),GETDATE(),120)+'-1')

[sdhdy]

select * from tb where datediff(mm,coldate,getdate())=0

[xiequan2]

select * FROM tb
WHERE Year(日期)=year(getdate()) and month(日期)=month(getdate())

[htl258_Tony]

-->本年:
select dateadd(dd,number,datename(yy,getdate())+'0101') 
from master..spt_values 
where type='p'
  and number<=datediff(dd,datename(yy,getdate())+'0101',datename(yy,getdate())+'1231')

[lfywy]

select * FROM tb
WHERE Year(日期)=year(getdate()) and month(日期)=month(getdate())

[htl258_Tony]

-->本年:
select dateadd(dd,number,datename(yy,getdate())+'0101') 
from master..spt_values 
where type='p'
  and number<=datediff(dd,datename(yy,getdate())+'0101',datename(yy,getdate())+'1231')
-->本月:
select dateadd(dd,number,datename(yy,getdate())+datename(mm,getdate())+'01') 
from master..spt_values 
where type='p'
  and number<day(dateadd(mm,1,getdate())-day(getdate()))

[mugua604]

select * from tableName where datediff(mm,col,getdate())=0

[lg3605119]

select * from tb where year(filed) = year(getdate()) and month(filed) = month(getdate())

[CHIC250]

[Quote=引用 5 楼 xiequan2 的回复:]
SQL code
select * FROM tb
WHERE Year(日期)=year(getdate()) and month(日期)=month(getdate())
[/Quote]
同意！

[dj3688]

select * from tableName where (datediff(yyyy,col,getdate())=0 and datediff(mm,col,getdate())=0)

[修炼成精]

如果数据量很大，注意查询效率 对日期建立索引 ，然后查询

[wxg22526451]

select * from tb where datediff(mm,coldate,getdate())=0

[wwle1985]

[Quote=引用 9 楼 mugua604 的回复:]
SQL code

select * from tableName where datediff(mm,col,getdate())=0
[/Quote]

这条比较简单实用

[仙道彰]

select * from tb where 日期 => CONVERT(char(5),@dt,120)+'1-1'
and 日期 <= CONVERT(char(5),@dt,120)+'12-31'

[仙道彰]

上面写错了。。。。
select * from tb where 日期 >= CONVERT(char(5),@dt,120)+'1-1'
and 日期 <= CONVERT(char(5),@dt,120)+'12-31'

[zzxap]

select * from tb where datediff(mm,coldate,getdate())=0 当月

select * from tb where datediff(year,coldate,getdate())=0 当年

[usher_gml]

select * from tb where datediff(mm,coldate,getdate())=0

[让你望见影子的墙]

select * from tb where datediff(mm，getdate(),日期)=0

[htl258_Tony]

楼主是要生成日期数据还是查找表数据？

[w398687283]

mark