Selecting this month only

[pwills]

Is there a more efficient query than the following?

SELECT * FROM WIDGETS WHERE
(DATEPART(m, MADE_ON) = DATEPART(m, GETDATE())) AND (DATEPART(yy, MADE_ON) = DATEPART(yy, GETDATE()))

 

[pwills]

PS. In my case, I am using SQL Server 7

 

[pwills]

Actually, my need is not quite "this month" but "last month".

SELECT * FROM WIDGETS WHERE
(DATEPART(m, MADE_ON) = DATEPART(m, GETDATE())) AND (DATEPART(yy, MADE_ON) = DATEPART(yy, GETDATE()))

does not work because of January. There must be some staightforward method but I cannot find it in BOL.

 

[pwills]

Perhaps

SELECT * FROM WIDGETS WHERE
DATEDIFF ('m', MADE_ON,GETDATE()) = 1

would work?

 

[kuroshivo]

does this work?

SELECT * FROM WIDGETS WHERE
(year(MADE_ON) = year(now()) AND month(MADE_ON) = month(now())-1) OR (year(MADE_ON) = year(now())-1 AND month(MADE_ON) = 12)