Looping over query by month

[fs22]

etings,

I have a query I am pulling that has a date field entitled, "Completed". I am attempting to loop over the query by date to slice and dice the data for a table and chart. Here is what I have done thus far...

Setup two variables where I am only interested in the month. My plan is to fileter the date by month so I can pull the data out by month.

    <cfset startDate = #DatePart('m','01/01/09')#>
    <cfset endDate = #DatePart('m',Now())#>

Here is my loop...

    <cfloop from = "#startDate#" to = "#endDate#" index = "i" step = "1">

Here is one of my QoQs within the loop...

        <cfquery name="NPS0" dbtype="query">
        SELECT *
        FROM rsNPS
        WHERE #DatePart('m',rsNPS.completed)# = #i#
        </cfquery>

I am having difficulties in getting this to work. Has anyone ever done something like this. I feel like the answer is right in front of me, but I have been staring at this code for a while. If anyone has any thoughts, I would be glad to hear them.

~Clay

[Dan_Bracuk]

Most db's have date and string functions that will enable you to get the month portion of the date.  Using them will be easier than using Cold Fusion.

Depending on your specific requirements, getting the year-month might be a better idea than getting just the month.

[-__cfSearching__-]

fs22 wrote:

        <cfquery name="NPS0" dbtype="query">

        SELECT *
        FROM rsNPS
        WHERE #DatePart('m',rsNPS.completed)# = #i#
        </cfquery>

QoQ are a separate beast. You cannot use standard CF functions inside them.  AFAIK, QoQ only support a few functions like CAST, UPPER, LOWER, etcetera.  So as Dan suggested, you should peform the date functions in your database query.