January 9, 2017 at 3:10 pm
I am trying to write a Select statement that will query a table that has a date field for all rows where the Invoice_Date is within the same month as todays month from the previous year.
I found another query that returns everything from the same month from the previous year, but only up to the current day of the month, instead of the entire month.
WHERE (Invoice_Date >= DATEADD(YEAR, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AND (Invoice_Date < DATEADD(YEAR, - 1, GETDATE()))
Any help is greatly appreciated.
January 9, 2017 at 3:16 pm
Something like this should do the trick:
WHERE Invoice_Date>=DATEADD(mm,DATEDIFF(mm,0,GETDATE())-12,0)
AND
Invoice_Date< DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0)
Cheers!
January 16, 2017 at 12:28 am
Would this work?WHERE YEAR(Invoice_Date) = YEAR(GETDATE()) - 1 AND MONTH(Invoice_Date) = MONTH(GETDATE())
January 16, 2017 at 2:23 am
John Corkett - Monday, January 16, 2017 12:28 AMWould this work?
WHERE YEAR(Invoice_Date) = YEAR(GETDATE()) - 1 AND MONTH(Invoice_Date) = MONTH(GETDATE())
Yes. It should, but the usage of functions in your WHERE clause makes your query non-SARGable and will prevent usage of indexes thereby impacting performance in a negative manner.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 16, 2017 at 2:28 am
Kingston Dhasian - Monday, January 16, 2017 2:23 AMJohn Corkett - Monday, January 16, 2017 12:28 AMWould this work?
WHERE YEAR(Invoice_Date) = YEAR(GETDATE()) - 1 AND MONTH(Invoice_Date) = MONTH(GETDATE())Yes. It should, but the usage of functions in your WHERE clause makes your query non-SARGable and will prevent usage of indexes thereby impacting performance in a negative manner.
Thank you. that's a very good point.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply