Coldfusion CFQUERY w/ Inner Join & Dateformat Not Working

Question

I am trying to wrap my head around dealing joins, mysql and coldfusion. The following query works without the last condition.

        <cfquery name="GetWeekends">
        SELECT w.id, w.weekend_type, w.community_id, w.start_date, w.end_date,
          w.language,
          c.community_id, c.location, c.language, c.state, c.country
        FROM _weekends w
        INNER JOIN _communities c
        ON w.community_id=c.community_id
        WHERE w.weekend_type = 1 AND w.start_date > Now() AND           
        #DateFormat(w.start_date, "m")# = '#form.home_by_month#'
        ORDER BY w.start_date ASC
        </cfquery>

It is dying on

  #DateFormat(w.start_date, "m")#

telling me variable [W] doesn't exist. Sorry, I am learning as I go here...

Answer 1

The issue here is that DateFormat() is a ColdFusion function, and that cannot be applied with a MySQL "variable" (w). You need to use the MONTH() function for MySQL and pass the date into that.

Don't forget to sanitize your form inputs, you are highly susceptible to SQL injection. Use cfqueryparam like so:

MONTH(w.start_date) = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.home_by_month#" />