CFHOUR: CFUnited 2010 Intervew #1 - Charlie Arehart

Aug 4

posted by Dave in CFUnited, cfdirectory | 0 Comments Bookmark and Share

Check out the interview w/ Charlie Arehart.

http://www.cfhour.com/post.cfm/cfunited-2010-intervew-1-charlie-arehart

This entry was posted on August 4, 2010 at 1:41 AM and has received 449 views. There are currently 0 comments. Print this entry.

Using Query of Queries with CFDirectory

Feb 17

posted by Dave in ColdFusion 9, cfdirectory | 3 Comments Bookmark and Share

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:

view plain print about
1<cfdirectory action="list" directory="f:\temp" name="cfdFileList" recurse="true">
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:

view plain print about
1<cfquery name="fileList" dbtype="query">
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

This entry was posted on February 17, 2010 at 11:04 AM and has received 911 views. There are currently 3 comments. Print this entry.