Auto delete records

[Flog]

I have an Events Calendar application that I want to automate a bit more. I
need a way to have coldfusion delete a record if its "Expiry date" is past
today's date.

I have tried a conditional statement with limited success.

Any ideas would be welcomed.

[Dan Bracuk]

delete from thetable
where expirydate <= current_date

the current_date part is database specific. You should know what it is for
your database. If this is not available in your datebase, you could always use
the appropriate dateformat on the cf now function

delete from thetable
where expirydate <= #d 'datefomat(now(), "yyyy-mm-dd")'}#

Originally posted by: Flog
I have an Events Calendar application that I want to automate a bit more. I
need a way to have coldfusion delete a record if its "Expiry date" is past
today's date.

I have tried a conditional statement with limited success.

Any ideas would be welcomed.

[mxstu]

> delete a record if its "Expiry date" is past today's date
As mentioned the exact syntax may depend on what type of database, column
type, and/or connection you're using.

Assuming "ExpiryDate" is a date/time column, if today is "10/29/2005"
something like the attached query should work. It would delete all records with
a date of 10/28/2005 or earlier (10/27/2005, 10/26/2005, etc...)

NOTE: You might consider updating a "flag" column (is. "IsDeleted" instead of
actually deleting the records. Especially if your events have related records
in other tables.


DELETE FROM yourTable
WHERE ExpiryDate < #CreateODBCDate(now())#