Thanks for the reply.  I was able to figure out my mistake.  It was my own fault of syntax and not having the quotes and such in the right place.  I've abandoned that query, but I will say thanks to all who helped.
----- Original Message -----
Sent: Monday, August 04, 2003 8:10 AM
Subject: RE: [KCFusion] DateDiff in SQL Query

You cannot treat CustomerEnrollmentDate as a CF Variable until you are outside of that query.  The following where clause should get you what you need:
WHERE CustomerRefer = #Affiliate_ID#
  AND CustomerEnrollmentDate between '#DateAdd('d',-7,createODBCDate(now()))#' and '#DateAdd('d',7,createODBCDate(now()))#'
-----Original Message-----
From: Robert [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 11:03 PM
Subject: [KCFusion] DateDiff in SQL Query

I am running into a problem and I can't figure out why.  I know i've had this problem before with using CF functions inside of SQL queries, but I can never remember what it was the last time that fixed it, so I wanted to post this query:
  <CFQUERY name="getcustomers" DATASOURCE="x">
  SELECT CustomerName, CustomerPin, CustomerRefer,CustomerEnrollmentDate
  FROM Customers
  WHERE CustomerRefer = #Affiliate_ID#
  AND #DateDiff("w",'#CreateODBCDate(CustomerEnrollmentDate)#','#CreateODBCDate(now())#')# = 0
I'm needing to run stats for affiliate programs and so I want a daily, weekly, monthly view.  Datediff in T-SQL didn't seem to be able to do anything other than generic day, month, etc.  Anyhow, I get the following error:
Error resolving parameter CUSTOMERENROLLMENTDATE
Obviously I have the right name, but it bombs right there...if I change around my quotes and my pound signs, it'll say it's Missing a parameter, Expected 1 or Expected 2.  Anyone easily point out what i've got in the wrong place or what I am messing up because i'm at a loss.  BTW, Affiliate_ID is from another CFOUTPUT from a query that this is all running inside of.

Reply via email to