RE: [KCFusion] DateDiff in SQL Query

[Kory Bakken]

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 To: [EMAIL PROTECTED] 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   </CFQUERY> 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.   Thanks, Robert