ColdFusion Query Parameterization

Question

I have a query that I run to populate CFChart, which I am trying to parameterize:

<cfquery name="total" datasource="#datasource#">
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = #month# 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")# 
    AND    technician_id = #techID#
 </cfquery>

Here is what I tried:

<!---Open tickets from chosen year where technician is active --->
<cfquery name="total" datasource="#datasource#">          
    SELECT *
    FROM   closed_tickets
    WHERE  MONTH(closed_date) = <CFQUERYPARAM Value="#month#"> 
    AND    YEAR(closed_date) = #dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"> 
    AND    technician_id = <CFQUERYPARAM Value="#techID#">
</cfquery>

When I change my request to this, it somehow breaks my CFChart. I do not have CFErrors on the screen, but my CFChart is empty.

I narrowed it down by contacting this in my query:

#dateFormat(theMonth,"yyyy")#" cfsqltype="CF_SQL_TIMESTAMP"

When I delete this parameterized part of the query and just put

#dateFormat(theMonth,"yyyy")#

it works.

Can anyone shed some light on this?

Answer 1

I do not have CFErrors on the screen, but my CFChart is empty.

, , cfsqltype . , (, , ), . . .

cf_sql_timestamp, "" /. YEAR() . , . :

  WHERE  2014 = {ts '2009-02-13 23:31:30'}

, , , / . , (.. ) (.. /). , , . , :

 WHERE 2014 = 1234567890

cfsqltype , , - .

• : , cfqueryparam "" cfsqltype (, , ..). , sql . , , . cfsqltype , .

• cfsqltype , . , , CF .

  cfsqltype , , , . . ( ).

  - , . , . "05/04/2014"? 5 4 ? . , .

- cfsqltype. / , , . YEAR() . cf_sql_integer, . MONTH().

 WHERE Year(ColumnName) = <cfqueryparam value="2014" cfsqltye="CF_SQL_INTEGER">
 AND   Month(ColumnName) = <cfqueryparam value="11" cfsqltye="CF_SQL_INTEGER"> 

, , Dan - . , () . cf_sql_date .

• cf_sql_timestamp -
• cf_sql_date - .

Answer 2

.

StartDate = CreateDate(TheYearYouWant, TheMonthYouWant, 1);
EndDate = DateAdd("m", 1, StartDate);

:

where closed_date >= <cfqueryparam cfsqltype="cf_sql_date" value="#StartDate#">
and closed_date < <cfqueryparam cfsqltype="cf_sql_date" value="#EndDate#">

, where.

Answer 3

, .

cfsqltype, .

<cfquery name="total" datasource="#datasource#">          
    select *
    from closed_tickets
    where MONTH(closed_date) = <cfqueryparam cfsqltype="cf_sql_integer" value="#month#">
          AND YEAR(closed_date) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#dateFormat(theMonth,"yyyy")#" > 
          AND technician_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#techID#">
</cfquery>

CF_SQL_VARCHAR , , , ( var char).