How to pull the past two years of data, but not the data in the current month in SQL-CodePudding

I am trying to pull the data from the past two years, starting from the the past month (not taking into account the current month). So it will pull all 2 years of data starting from 09/30/2021. I've tried the following, but the months after September for 2020 get taken out:

WHERE YEAR(ACCDAT_0) >= (YEAR(GETDATE()) -2) AND MONTH(ACCDAT_0) < MONTH(dateadd(dd, -1, GetDate()))

CodePudding user response：

DECLARE @d date = GETDATE();
SET @d = DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);

SELECT ... WHERE ACCDAT_0 >= DATEADD(YEAR, -2, @d)
             AND ACCDAT_0 <  @d;

Several helpful date articles here, including why you want range queries and not things like YEAR(column): Dating Responsibly

CodePudding user response：

You may try the following logic:

SELECT *
FROM yourTable
WHERE ACCDAT_0 >= DATEADD(year, -2, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AND
      ACCDAT_0 < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);