Purging Away of Past dated data

[SnowyMeowy]

Hi experts, I have a query which I hope you all can help me.

I have this system whereby it will check for the date as included in the Database. After about two weeks of the date, the system should be able to automatically delete the past dated data from the system. Can anyone tells me how i should go about doing this or is there any sample codes for reference?Thanks.

 

[Tarwn]

Well, heres an easy way to do it. Since you are already checking the date on a daily basis, have your program create a calendar object as well. Get the current date, subrtract two weeks.Now,

1) Assuming you are using SQL dating system:
Convert the date to SQL dating system (you can use the sql.date object).
Then &quot;SELECT * FROM database WHERE date < &quot;+yourDate+&quot;;&quot;
This will give you a resultset (rs) of all values in the database that are older than two weeks. Now we want to go through and erase them.
if(!rs.isBeforeFirst){
//emtpy set, since we have next'd yet, isBeforeFirst
// will be true if there is anything in the set, false
// if there isn't
}
else{
while(rs.next())
//here is where you will put your delete statement, I
// assume that your primary key is in the first position
// in the table, you could also use the attribute name
// instead of column number
//&quot;DELETE FROM database WHERE yourPrimaryKey =&quot;+rs(1)+&quot;;&quot;
}

2) OK, that was assuming your using SQL dating. If you are not, than it gets more fun. I assume you have a consistent dating system. It would be nice to put all the date deletions in the code, and that could be done with a good while loop, but it would keep it cleaner (as long as your opening this app every day) if you just had it delete the date from two weeks ago. So you would do something similar.
Create a calendar object, get the date for two weeks ago + 1 day. Convert it into the dating format you use, than select from the database where the date is equal to your date.
Turn around, follow the ResultSet instructions from above...

an easier way would be to simply send the database the line
delete from wherever where date = yourDate

Hope this was helpful and not to confusing,