T-SQL select records for the month

I have a table called logD, with a field named date (datefield type). Format is "year-month-day" IE: 2011-04-11

If today's date is 2011-07-31, I want all the records for the month of July. If today's date is 2011-02-14, I want all the records for the month of Feb and so on.

I am using SQL 2008 and reporting services to run a monthl开发者_如何学运维y report.

Try this:

SELECT *
  FROM logD
 WHERE YEAR(DATE) = YEAR(GetDate())
   AND MONTH(DATE)  = MONTH(GetDate())

If you have an Index on the column DATE then you can try this one:

SELECT *
FROM logD
WHERE [DATE] BETWEEN CONVERT(VARCHAR(6),GETDATE(),112)+'01' AND DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(VARCHAR(6),GETDATE(),112)+'01'))

But, man, it looks ugly...

Try this:

DECLARE @firstOfMonth smalldatetime = dateadd(month,datediff(month,0,getdate()),0);

SELECT * FROM logD
WHERE [date] > @firstOfMonth 
AND   [date] < dateadd(month,1,@firstOfMonth);