coldfusioncoldfusion-9cfdirectory

group cfdirectory by dateLastModified (date not time)

I am running ColdFusion 9 and Oracle database. I am trying to group all files from a directory by the dateLastModified using:

<cfdirectory action="LIST" 
    directory="#path_to_files#" 
    name="res" 
    sort="datelastmodified desc,name ASC" 
    filter="#filters#">

For example:

File 1 & 2 should be group 1 and File 3 should be group 2

If I use a query of query, how can I compare the date based on days not time? This is what I have so far but it does not seem to work. I get no records.

    <cfquery name="getfiles" dbtype="query">
        SELECT name, datelastmodified
        FROM   res
        WHERE  CAST( [datelastmodified] as date) = CAST(<cfqueryparam  cfsqltype="CF_SQL_DATE" value="#d#"/> as date)
    </cfquery>

Does anyone know a different method to group files based on days not time?

Solution

  • So I figured it out. Thanks to @Dan and @Leigh for their suggestion. I used both as guides to get what I wanted.

    1. I used cfdirectory to get a query object.
    2. I created a new query using QueryNew, QueryAddRow and QuerySetCell.
    3. On the columns of the new query contained the formatted date (mm/dd/yyyy). Make sure that you declare the column as varchar not date when setting the column names QueryNew.

    4. I used cfloop and group option and cfoutput to display the records.

      <cfset path_to_files = "d:\inetpub\wwwroot\mailrideli\webrpt\">
<cfset filters = "*.pdf|*.txt|*.xls">
<cfdirectory action="LIST" directory="#path_to_files#" name="res" sort="datelastmodified desc,name ASC" filter="#filters#">

<cfset q = QueryNew("Name,dateformated,datelastmodified, size","Varchar, Varchar, date, varchar")>
<cfset count = 1>
<cfset newRow = QueryAddRow(q,res.recordCount)>

<cfloop query="res">
    <cfset d = DateFormat(res.dateLastModified,"mm/dd/yyyy")>
    <cfset temp = QuerySetCell(q, "Name", "#res.name#", count)>
    <cfset temp = QuerySetCell(q, "dateformated", "#d#", count)>
    <cfset temp = QuerySetCell(q, "datelastmodified", "#res.datelastmodified#", count)>
<cfset temp = QuerySetCell(q, "size", "#res.size#", count)>

<cfset count += 1>
</cfloop>

<cfoutput>
    <cfloop query="q" group="dateformated">
                    #q.dateformated#<br />


                    <table>
                        <tr>
                            <th>File Name</th>
                            <th>Size (bytes)</th>
                            <th>Last Modified</th>
                        </tr>
                        <cfloop>

                            <tr>
                                <td><a href="#q.name#">#q.name#</a></td>
                                <td>#q.size#</td>
                                <td>#dateformat(q.dateLastModified, 'mm/dd/yyyy')# #timeformat(q.dateLastModified, 'hh:mm:ssTT')#</td>
                            </tr>

                        </cfloop>
                    </table>
    </cfloop>
    </cfoutput>

    I hope it helps anyone out there.