Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unanswered: Purging of data in db2 tables

    Hi all,

    I am writing a script to purge (remove) data which is 30 days old than
    the present date. Now how do i develop this script in such a way that
    it runs in crontab. I have used the statement like


    db2 "delete from orders21 where order_time <
    '2006-12-01-06.53.46.000000'".


    Now is there a statement like


    db2 "delete from orders21 where order_time <
    'date-2006-12-01-06.53.46.000000'". Where date is present day date.


    And is there a way to print the date in the same format
    (2006-12-01-06.53.46.000000)? Because when we print date in UNIX it
    gives the following format.


    Tue Jan 2 11:03:57 EST 2007.


    I have also seen this format in groups google


    delete from orders21 where (today - order_time) > interval '30' day .


    It hasn't worked for me.


    Thanks in advance,


    Chaitanya

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Well assuming that the "ORDER_TIME" column is a timestamp, this should work:

    DELETE from orders21 where ordertime < (current timestamp - 30 days)

    HTH

    Andy

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    wow Andy,

    awesome. Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •