Skip to main content
B
brianism
Inspiring
June 7, 2010
Answered

Problem with query and date

  • June 7, 2010
  • 1 reply
  • 514 views

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>

    This topic has been closed for replies.
    Correct answer ilssac

    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">

    1 reply

    ilssac
    ilssacCorrect answer
    Inspiring
    June 7, 2010

    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">

    B
    brianismAuthor
    Inspiring
    June 7, 2010

    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>

    D
    Dan_Bracuk
    Inspiring
    June 7, 2010

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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices