Scheduled removing of data

[Leakyradiator]

Hi:

I have an event calendar. Each Event has a StartDate and an EndDate. I want to set up a scheduled event so that the record will be deleted 1 day after the End Date.

I'm not sure how to write instructions to do this. (and I don't want to wipe out my database trying to figure it out!). Any and all advice is greatly appreciated.

Thanks

 

[philhege]

Leaky,

I assume that you are archiving this data somewhere beforehand.

What DB? (I wish they had a profile section here, so I wouldn't have to keep asking this question!)

Phil Hegedusich
Senior Web Developer
IIMAK
<a href=&quot;

http://www.iimak.com&quot;>http://www.iimak.com</a>

-----------
Boy howdy, my Liberal Studies degree really prepared me for all of this....

 

[Leakyradiator]

Sorry...I should've been more complete in my info.

I'm using SQL Server 2000 & Cold Fusion 5.0.

 

[ecobb]

You may want to try something like this (it's off the top of my head so I haven't tested it.)

<!--- SET the date to today --->
<cfset CurrentDate = #Now()#>
<cfset DeleteDate = &quot;#DateFormat('#CurrentDate#', 'mm/dd/yy')# 00:00&quot;>

Notice that I put the &quot;00:00&quot; in the #DateFormat#. Since SQL Server generally keeps time as well in the smalldatetime data types, you will need this to keep from deleting anything from the current day. The #Now()# function will set the current date AND time, so by using #DateFormat# with &quot;00:00&quot; it will replace the current time. That way, if an event ends today, it won't be deleted until tomorrow.

<!--- delete all entries that have an EndDate before today's date --->
<CFQUERY NAME=&quot;delete_old_events&quot; DATASOURCE=&quot;#data#&quot;>
DELETE
FROM dbo.TableName
WHERE EndDate < #DeleteDate#
</CFQUERY>

It's a rough draft, but maybe it's pointing you in the right direction. Like I said, it's off the top of my head so you may want to test it on a dev machine so you can tweak it before it goes live.

Hope this helps!