automatically archive records in the database

i can't schedule without using coldfusion. i have to get the date field from the database and compare with today's date, if it's more than 1 month then update the database table with a field to archive. i don't want to recode anything.

thanks.

quote:

---

Originally posted by: thnguyen
Hi,
In the database, there is a field with archive date like 4/5/2006 and another field with 1 for archive and 0 not yet archive. What is the best way to do an automtically archive any records more than 1 month by comparing the archive date in the db with today's date?
Thanks

---

The best way is a matter of opinion. Mine is to not use a flag but instead to use your db's date functions to apply whatever criteria you have for archiving records. Also, if you can, it is best to do this as a scheduled job without using cold fusion.

This will change the bit field from 0 to 1 if the archive date is a month old or older. I think that's what you wanted to do.

<!--- Set the date to one month before now --->
<cfset ArchiveDate = DateAdd("m",-1,Now())>

<cfquery ... >
UPDATE yourTable SET
yourArchiveBitField = 1
WHERE yourArchiveDateField < '#ArchiveDate#' AND yourArchiveBitField = 0
</cfquery>