How can I Delete an item if older than x amount of days 1

[mjstark]

Does anyone know how do do a delete using CF/SQL that will delete items if they are older than a certain amount of days?

I am writing an classifieds application and I want it to delete any ads that are older than 30 days. When the ad is posted it inserts the current date into the database in the following format mm/dd/yyy. When a user queries the ads I want a delete statement to run first that will look at the ads and then delete the ones that are older than 30 days. However I am not sure how to write the where statement in the query. Can anyone help me with this?

 

[Silvano]

the following query will select all fields that are older then today's date minus 30 days; make sure that Data Type of the date field in the data base is Date/Time;


<CFQUERY NAME=&quot;queryName&quot; DATASOURCE=&quot;dbn&quot;>
SELECT *
FROM tableName
WHERE dateField < #Now()# - 30
</CFQUERY> Sylvano
dsylvano@hotmail.com

 

[mjstark]

Thanks, I changed it to a delete statement and it works great. I appreciate the help.

 

[danielhai]

it's better to use stored procedures. Use CFQuery to call your delete procedure. You don't have to pass in CF Time if you don't want, just use:

delete from tablename where dateField < getdate()-2

the -2 is for days(the default SQL setting).

 

[sevex]

I use this:

DELETE FROM news WHERE date < #DateAdd('D', -30, createODBCdate(Now()))#