DBMSguy.com

Have you ever had a situation where you needed to select rows from a table for a specific month?   This example demonstrates how to retrieve all of the records belonging to the previous month by setting the first and last days in the WHERE clause of a SELECT statement.   But with some tweaking, you can make this query retrieve data from any month you wish.

For this example, we’ll assume that we have a table in a data warehouse called DW.TRANSACTION with two columns (TRANSACTION_ID and EFFECTIVE_TS) that is populated and up to date.

To retrieve the previous month’s records, we would create a query of the form:

SELECT
       TRANSACTION_ID
      ,EFFECTIVE_TS
FROM
       DW.TRANSACTION
WHERE
       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS) - 1 MICROSECOND
;

If you would like to retrieve data from two months ago, the WHERE clause would be changed to:

       EFFECTIVE_TS >= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 2 MONTHS)
AND    EFFECTIVE_TS <= TIMESTAMP_ISO(DATE(CURRENT DATE) - (DAY(CURRENT DATE)-1) DAYS - 1 MONTH) - 1 MICROSECOND

At this point, you can keep increasing the values for the MONTHS to keep going into the past.