Problem with query and date

[brianism]

I'm trying to only show particular results in my query based on the following:

If the "agreedate" is greater than today's date, minus 60 days, show it. Otherwise I don't want the results showing.

I am still getting results showing from last year and I don't know why.

Here is my code:

<!---set todays date minus 60 days. This is working and shows the correct date--->

<cfset invitelimit = #now()#>
<cfset invitelimit = #DateFormat(DateAdd('d', -60, invitelimit),'mm-dd-yyyy')#

<!---query the items from the db and try to only grab the ones with an agreedate greater then the invitelimit date--->

<cfquery name="qinvite" datasource="#Application.ds#">

SELECT DISTINCT * FROM team a

INNER JOIN teammembers b ON a.id = b.team_id
INNER JOIN termsagreement c ON a.login = c.login

WHERE c.agreedate > #invitelimit# AND b.comp_id = 0

Group By a.teamname

</cfquery>

[ilssac · Accepted solution]

First of all.  Confirm that the 'argeedate' column is a datetime column.  Not some other data type that would be much harder to work with.

Secondly, you should be providing a proper database datetime value.  The easiest way would be with the <cfqueryparam...> tag.

I.E.

WHERE

c.agreedate > <cfqueryParam value="#invitelimit#" cfsqltype = "cf_sql_timestamp"> AND

b.comp_id = <cfqueryparam value="0" cfsqltype = "cf_sql_integer">

[brianism]

Thanks Ian, you did it again!  My "agreedate" column is set to "date" in a MySQL database, not sure if that matters.  Looks like changing my query to your suggestion did the trick. I always forget to use cfqueryparam in my queries:

<cfset invitelimit = #now()#>
<cfset invitelimit = #DateFormat(DateAdd('d', -60, invitelimit),'mm-dd-yyyy')#>

<cfquery name="qinvite" datasource="#Application.ds#">

SELECT DISTINCT * FROM team a

INNER JOIN teammembers b ON a.id = b.team_id
INNER JOIN termsagreement c ON a.login = c.login

WHERE c.agreedate > <cfqueryParam value="#invitelimit#" cfsqltype = "cf_sql_timestamp">  AND b.comp_id = <cfqueryparam value="0" cfsqltype = "cf_sql_integer">

Group By a.teamname

</cfquery>

[Dan_Bracuk]

Another approach is to use mySQL date functions.  Then you don't have to worry about cf variables.