group cfdirectory by dateLastModified (date not time)

Question

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:

• Date of File 1: 2016-10-03 11:49:00 am
• Date of File 2: 2016-10-03 07:49:00 am
• Date of File 3: 2016-08-03 07:49:00 am

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?

Answer 1

method to group files based on days not time

To group by Date (only), add the "date only" column to your SELECT list. Then ORDER BY the new column:

<cfquery name="qSortedFiles" dbtype="query">
    SELECT CAST(DateLastModified AS Date) AS DateOnly
            , Name
            , DateLastModified 
    FROM   getFiles
    ORDER BY DateOnly
</cfquery>

<cfoutput query="qSortedFiles" group="DateOnly">
    #DateOnly#<br>
    <cfoutput>
        - #DateLastModified#<br>
    </cfoutput>
</cfoutput>

I get no records.

FWIW, the reason is that despite using CAST ... as Date, apparently CF still preserves the "time" portion internally. So the WHERE clause is still comparing apples and oranges:

  <!--- comparing Date and Time to Date (only)
  WHERE 2016-08-03 07:49:00 am  = 2016-08-03 00:00:00 am

Instead of using an equals comparison, use this approach:

    WHERE DateLastModified >= {TheStartDateAtMidnight}
    AND   DateLastModified < {TheNextDayAtMidnight}

This type of comparison is more flexible as it works with both date and time columns - and dates (only).

WHERE  CAST(DateLastModified AS Date) >= <cfqueryparam value="#someDate#" cfsqltype="cf_sql_date">
AND    CAST(DateLastModified AS Date)  < <cfqueryparam value="#dateAdd('d', 1, someDate)#" cfsqltype="cf_sql_date">

Answer 2

Step 1 - Use cfdirectory to get a query object.

Step 2 - Add a column using queryaddcolumn.

Step 3 - Loop through the query. Use querySetCell and dateformat on your new column

Step 4 - use cfoutput, group by your new column, to do what you need to do. Your cfdirectory tag already has the data sorted.

Answer 3

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.