george.greiner (12/5/2012)
I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).
Firstly, when looking for a month of data you're better off with this: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
Which allows the query optimiser to seek on any index that may be on your DateFinished column.
Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?
Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.