I have to delete 30 days old data based on timestamp in DB2

user_13683060 0
Viewed 217

I have a requirement to delete 30 days old record in DB2. I used below code to delete, It worked fine but current timestamp keep changing. This cause my procedure to go on loop.

DELETE FROM TABLE 
WHERE TIMESTAMP_FIELD < CURRENT TIMESTAMP - 31 DAYS

I'm using my procedure as generic delete, So I will not able to use host variable. I tried subdate ADDDATE but no luck.

Please help me out.

Thanks

cobol dynamic-programming db2 stored-procedures
1 Answers

Current Timestamp is constantly changing so you're liable to chase your own tail if you use it for comparisons down to the microsecond level.

If you need a less precise value to use for deletions you can cast the current date - 31 days together with '00:00:00' (midnight). This value will only change when the current date does so your results are more predictable.

  DELETE FROM TABLE 
  WHERE TIMESTAMP_FIELD < TIMESTAMP(CURRENT DATE - 31 DAYS, '00:00:00')
user_110256080
Related