Search code examples
sqlibm-midrange

Delete records older than 3 months in AS/400

I have a table called transactions which contains Customer Name (custname), Transaction Date (trxdate). I need to delete values older than 3 months.

trxdate is CHAR (6) in format YYMMDD like 220611.

I am using this in an SQLRPGLE but it is not working meaning that the table does not get modified after running it, I tried that too from DBeaver for the same result.

Delete from Trans WHERE trxdate < CURRENT DATE- 3 MONTHS;

I am thinking that CURRENT DATE is wrong, so how to convert that to YYMMDD?

Solution

  • If you are using DB2 you can try:

    select TO_DATE('220611', 'YYMMDD') trx from SYSIBM.SYSDUMMY1;

    First, you need to convert char data to date type to compare.

    Delete from Trans WHERE TO_DATE(trxdate, 'YYMMDD')< CURRENT DATE- 3 MONTHS;

    For other database systems, the logic is same.