Using Query of Queries with CFDirectory
Feb 17
posted by Dave in
ColdFusion 9, cfdirectory
| 5 Comments
So.. I was using CFDirectory to get a list of files. I then wanted to use the result and use Query of Queries to get files that had a modified date more than 2 days ago.
Sounds pretty basic right? I thought so too. Then I wrote the code and found out otherwise.
This was the code I started with:
2
3<cfquery name="fileList" dbtype="query">
4 select *
5 from cfdFileList
6 where type = 'File'
7 and datelastmodified < #dateAdd( "d", -2, now())#
8</cfquery>
No matter what this always returned 0 results. After some trial and error I figured out the date comparison in the where clause was failing. From what I could tell ColdFusion was not treating the data types as dates.
I tried everything I could think of to get CF to treat them as dates. Then, John Dowdle pointed me to using CAST() on the dates.
First couple tries did not work. So, with my new found knowledge I hit the interwebs to see how to do it. After a quick search I found a post by Ben Nadel about this very subject. However, Ben's blog post was a few years old so I was not sure if it was still relevant. I figured I got nothing to loose at this point so I used his example.
What I ended up with was this:
2 select *
3 from cfdFileList
4 where type = 'File'
5 and CAST( [datelastmodified] as date) < cast(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#dateAdd( "d", -2, now())#"/> as date)
6</cfquery>
I had to use cast on both sides of the comparison to get it to work. Also, it would fail if I did not use cfqueryparam. This just seems like very strange and odd behavior. One would think that ColdFusion could handle the data types much better than this.
BTW... I am using ColdFusion 9 so I don't see this going away anytime soon.
Till next time...
--Dave
#1 by Jochen on 2/19/10 - 5:15 AM
I was working on the same problem filtering results of a cfdirectory.
I tried to use the CreateODBCDate and it worked for me. Also running on CF 9.
Here the 'complete' code:
<cfdirectory action="list" name="docs" directory="#expandpath("../")#" type="file" filter="*.cfm">
<cfquery name="newdocs" dbtype="query">
SELECT *
FROM docs
WHERE datelastmodified > #CreateODBCDate(now()-2)#
</cfquery>
<cfdump var="#newdocs#">
#2 by Eric Cobb on 2/25/10 - 8:13 AM
#3 by chris on 5/12/12 - 6:15 AM
#4 by Wade Stockton on 6/8/12 - 2:53 AM
#5 by MCITP on 5/8/13 - 8:52 PM
Microsoft Certified IT Professional certification is one of the elite and globally recognized credentials that provide IT professionals the required edge with a far more superior and targeted framework to display their technical and professional skills.
<a href="http://www.bestmscert.com/exam-70-642-7.html"...;