Getting Data fro Same Month Last Year

  • 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.

  • 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!

  • Would this work?
    WHERE YEAR(Invoice_Date) = YEAR(GETDATE()) - 1 AND MONTH(Invoice_Date) = MONTH(GETDATE())

  • John Corkett - Monday, January 16, 2017 12:28 AM

    Would 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.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian - Monday, January 16, 2017 2:23 AM

    John Corkett - Monday, January 16, 2017 12:28 AM

    Would 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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply