Purging away of past dated data 1

[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.

 

[sknyppy]

Use the scheduler in cf administration - schedule a task to run daily to check for expired data and have it delete past dated data from the system. I use this in a classified ad system that removes free ads after 30 days. You just set it up and forget about it, let the server do the work.

Dave

 

[CFHub]

Yes, that is pretty much all there is to it

If you have never set up a CFSCHEDULE this may help:

http://cfhub.com/advanced/cfschedule/

The SQL BETWEEN operator works great for scripts like this:

http://cfhub.com/faq/date_inserts.cfm

 

[strantheman]

As far as the template goes, you need to use the dateDiff() function.

There's a better way to do it with SQL only, but this should do what you need:


Set the age, today's date, and prep your list.

<!--- age in days --->
<CFSET age = 14>
<CFSET dateToday = createODBCdate(now())>
<CFSET delList = &quot;&quot;>

<CFQUERY name=&quot;cleanup&quot; datasource=&quot;#ds#&quot;>
select id,theDate
from myTable
</CFQUERY>

Check the date against today's date. If it is less than or equal to -14, add the ID of that date to the list.

<CFOUTPUT query=&quot;cleanup&quot;>
<CFIF dateDiff(&quot;d&quot;, dateToday, theDate) LTE -age>
<CFSET delList = listAppend(delList, id)>
</CFIF>
</CFOUTPUT>


Delete those IDs.
<CFQUERY name=&quot;delete&quot; datasource=&quot;#ds#&quot;>
delete from myTable
where id in
(
#delList#
)
</CFQUERY>

 

[strantheman]

Let me know if I helped.

 

[SnowyMeowy]

hello sknyppy,
I have tried using scheduler but i was having some problems with it.

<cfschedule action=&quot;delete&quot;
task=&quot;purge_records&quot;
operation=&quot;httprequest&quot;
url= &quot;

http://127.0.0.1/Rejoice/Admin/Purge/purge_records.cfm&quot;

interval=&quot;weekly&quot;>


<cfset x = #DateFormat(Now())# - 14>
<cfif #DateCompare(DateFormat(Now()))#, x =1>
<CFQUERY DATASOURCE = &quot;rejoice&quot;>
DELETE FROM Reservations
WHERE Reservation_Date = x
</cfquery>
</cfif>

I do not know exactly how i should work with the scheduler. Please correct me if there's anything wrong with the above codes.