Teradata SQL - how to get data between current month and current month+8 (current month+8 will fall into 2023)

Question

I have this code but returns 0 row:

SELECT
EXTRACT(MONTH FROM POST_DATE)
FROM
MY_TABLE

WHERE

EXTRACT(MONTH FROM POST_DATE) BETWEEN EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE,8))

Now it's month 5, and if my code changes to 7 instead 8, the result is showing 5,6,7,8,9,10,11,12. And MY_TABLE has data for 2023. Can anyone please help? Thanks.

Answer 1

Here we calculate the first day of this month, then we add 9 months to the last day of last month.

SELECT
EXTRACT(MONTH FROM POST_DATE)
FROM
MY_TABLE
WHERE POST_DATE BETWEEN
  ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1, 0)
AND
  ADD_MONTHS(CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE), 9)
;