try that first one, I assume your date column in the DB is storing just the date and not the datetime? if it's storing datetime there would be a problem and you'd have to cast that as a date too.
On Wed, Nov 14, 2012 at 7:52 AM, Greg Morphis <[email protected]> wrote: > cast(dateadd('y', -1, getdate()) as date) as -- getdate() in MSSQL > or > trunc(dateadd('y', -1, sysdate)) -- sysdate in Oracle > > > > > On Wed, Nov 14, 2012 at 7:46 AM, Greg Morphis <[email protected]> wrote: > >> What DB are you using? >> >> >> On Wed, Nov 14, 2012 at 7:43 AM, Eric Bourland <[email protected]>wrote: >> >>> >>> Greetings. I need some advice again. I need to use CFSCHEDULE to >>> schedule a >>> task that does the following: >>> >>> >>> >>> * review all records in table 'membersTable', once per day >>> >>> * return records that are one year (365 days) old, then two years old, >>> then >>> three, and so on in increments of years >>> >>> * send a renewal reminder to the UserEmail associated with each found >>> record >>> >>> >>> >>> But I am having trouble building the query to return records that are >>> increments of one year old. How can I form this this query? >>> >>> >>> >>> The column "DateCreated" notes the date when a record was first created. >>> >>> >>> >>> <!--- query membersTable for records that are increments of one year old >>> ---> >>> >>> <cfquery name="getUser" >>> datasource="#APPLICATION.dataSource#"> >>> >>> SELECT UserID, FirstName, LastName, >>> UserEmail, DateCreated >>> >>> FROM #REQUEST.membersTable# >>> >>> WHERE DateCreated = (DateCreated + >>> increments of one year) >>> >>> </cfquery> >>> >>> >>> >>> How should I form that WHERE clause? Thank you for any advice. >>> >>> >>> >>> Eric >>> >>> >>> >>> >>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:353159 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
