Get the data between previous 11 months till current month using SQL

Question

Help me with the below question.

I have ordermonth which is in BigInt format starting from 201801 till 202912. I need to get the records where ordermonth from last 11 months till current month.

How to achieve this?

Thanks in advance.

Answer 1

You can use the YEAR and MONTH functions with a bit of arithmetic

WHERE ordermonth >= YEAR(DATEADD(month, -11, GETDATE())) * 100 + MONTH(DATEADD(month, -11, GETDATE()))
  AND ordermonth <= YEAR(GETDATE()) * 100 + MONTH(GETDATE())

I would advise you to store ordermonth as an actual date. You could store either the beginning or the end of the month (you can get that with EOMONTH())