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.
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)
;