Seeking SQL to pull data from current and prior year only

[rdasilva]

Hello, 

Anyone know the correct code to pull data from current and prior year only? 

Thank you all. 

[FrederikE]

Try: 

where

YEAR("INVOICE_DATE_PK")='2021' OR 

YEAR("INVOICE_DATE_PK'")='2022'

Works only if the field is stored in the database as a data field type. Otherwise, you could try to use SUBSTRING/LEFT. 

[DataNath · Accepted solution]

Try replacing your ‘where’ line with:

WHERE YEAR(“INVOICE_DATE_PK”) > 2020

[Robin_McIntosh]

And if you want to make it dynamic, you could use the below WHERE statement and then update YRS by passing the number of years to go back.

WHERE year(invoice_date_pk) >= year(current date) -YRS

[rdasilva]

Hi Robin - Thank you for the suggestion. I tired but did not work. 

[DataNath]

@rdasilva I believe Robin just put 'current year' and 'YRS' there as placeholders. To get this working in your case it'd be

WHERE YEAR(“INVOICE_DATE_PK”) >= YEAR(GETDATE()) -2

Where you would change the -2 as mentioned if you wanted to go further back any more years.

[Robin_McIntosh]

@rdasilva This was based on DB2 SQL so the "current date" reference could be different depending on what you're using.