Current month minus 5 months for all years worth of data-CodePudding

I am trying to get data for the month 5 before the current one. I have tried

MONTH(GETDATE()) - MonthNum ='5'

Where monthnum has been parsed from the date in a previous CTE.

This works for the 6th month and beyond but doesn't for earlier months.

The end goal is, for example in November I can see data only for June, for all years not just the current one.

SELECT * 
FROM TABLE 
WHERE MONTH(GETDATE()) - AnniMonthNum ='5'

CodePudding user response：

From your clarification, try this:

DECLARE @month = MONTH(DATEADD(month,-5,GETDATE())); 

SELECT t.* 
FROM table as t 
WHERE MONTH(t.transaction_date) = @month;

or, if using in a view, substitute the calculation directly in the WHERE clause, like this:

SELECT t.* 
FROM table as t 
WHERE MONTH(t.transaction_date) = MONTH(DATEADD(month,-5,GETDATE()));

If you already have the number of months calculated, then substitute that variable for the interval in the DATEADD function. Below I make sure the interval is negative by multiplying monthnum by -1.

SELECT t.* 
FROM table as t 
WHERE MONTH(t.transaction_date) = MONTH(DATEADD(month,-1 * monthnum,GETDATE()));

CodePudding user response：

try using DateDiff

  where DateDiff(month,AnniversaryDate,GetDate)=5