teradatateradata-sql-assistantteradatasql

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


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.


Solution

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