george.greiner (12/5/2012)
Cadavre (12/5/2012)
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.
When I use the code I provided I get no results and as of today there should be 7.
When I use your code I get a syntax error.
Yes, for some reason the forum changed < to & l t ; and > to & g t ;
It should read: -
WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
This is irrelevant though. Without the sample data showing your issue so that we can have a look, it's pretty impossible to know why you aren't getting the results you require. As in my previous post, please take a look at this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which explains how to post sample data.