----- 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()))#'
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
