Date Function for DB2

问题:

I have a file which has transaction details in DB2 and my requirement is extract last 1 months transaction data. Currently I amend the date range manually as bellow.

WHERE TRAN_DATE BETWEEN 20170115 and 20170214

Is there a way to code without manually adjusting the dates daily?

回答1:

How about using current date and date arithmetics?

WHERE TRAN_DATE BETWEEN current date and (current date - 1 month)

If you need the YYYYMMDD format, you can convert like this:

YEAR(CURRENT DATE) * 10000 + MONTH(CURRENT DATE) * 100 + DAY(CURRENT DATE)

回答2:

If TRANS_DATE is a number then

WHERE TRANS_DATE BETWEEN INTEGER(TO_CHAR(CURRENT DATE,'YYYYMMDD')) AND 
                         INTEGER(TO_CHAR(CURRENT DATE - 1 MONTH,'YYYYMMDD'))

If it is a string then

WHERE TRANS_DATE BETWEEN TO_CHAR(CURRENT DATE,'YYYYMMDD') AND 
                         TO_CHAR(CURRENT DATE - 1 MONTH,'YYYYMMDD')