asked on

cfschedule cfmail containing hit results with start date set to date column in db

I have a tricky cfschedule/ cfmail task- My site is almost done and I need to try to keep it completely automated since I have a real job. I already have welcome mail that is sent with results of their db insert at enrollment time and it works great. Now I need to email a news letter (eventually will be a billing statement when site is worth charging for) complete with individual hit results to my members on a monthly basis. I have a column in dbo.members called "member_since" which contains the date/ time of insert (enrollment). Here is what I have but I know the syntax is wrong before I even run it. Please keep in mind, I need this to happen on every 30 day anniversary for every member. I'll need to change the static part of the message body from time to time but I got the cfmail part down pretty well. Thanks in advance, Andy- electriciansnet.com

<cfquery name="schedMembers" datasource="#enet#" maxrows="1">
SELECT CompanyName, member_Since FROM dbo.members
</cfquery>

<cfschedule action = "update"
task = "Status Report For #CompanyName#"
operation = "HTTPRequest"
url = "mailer/mailer_status.cfm"
startDate = "#schedMembers.member_Since#+#CreateTimeSpan(30,0,0,0)#"
startTime = "12:25 PM"
interval = "monthly"
resolveURL = "Yes"
publish = "No"
requestTimeOut = "600">

Scott Bennett

You should be using dateformat for that start date like:

<cfschedule action = "update"
      task = "Status Report For #CompanyName#"
      operation = "HTTPRequest"
      url = "mailer/mailer_status.cfm"
      startDate = "#dateformat(schedMembers.member_since,'mm/dd/yyyy')#"
      startTime = "12:25 PM"
      interval = "monthly"
      resolveURL = "Yes"
      publish = "No"
      requestTimeOut = "600">

ASKER CERTIFIED SOLUTION

Scott Bennett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Electriciansnet

ASKER

Upon further research I agree with your last comment. I figured out that I can create 1 task that points to mailer/mailer_status.cfm with an interval of 1 or 2 times daily and put a query like this at the top of mailer/mailer_status.cfm:
<cfquery name="<cfquery name="getUsers" datasource="#enet#">
select* from members
where datepart(day, member_since) = datepart(day, getdate())
</cfquery>
But this creates a new problem which is a) to detailed to put in this question/ b) not related to cfschedule
I'm going to go post that Q now