I need to loop through <cfdirectory

[nikos101]

I need to loop through this query and add a new column to each row in fileQuery query which contains the a date object represented in the filename

<cfdirectory directory = "#dir#" name = "fileQuery" sort = "datelastmodified">

the fileQuery.fileName is of the following format

staffID1247date12_30_2000.flv

(note the date is MM DD YYYY)

Could anyone advise?

Message was edited by: nikos101

[Dan_Bracuk]

You can use string and list functions to extract the string and parsedatetime to convert the string to a date. 

[nikos101]

Ok, but how would I parse out the date using regex?

[Adam Cameron.]

Which part of creating the regex are you having trouble with?

My approach would be to match the entire string, making sure to capture subexpressions for the dd, mm & yyyy parts.  Then replace the whole thing with \3-\2-\1 (where those are yyyy, mm, dd).  That will leave you with an unambiguous string which you can pass into parseDateTime() to create your date object.

--

Adam

[nikos101]

I think the regex I should use is

.*date(\d{2})_(\d{2})_\d{4}

staffID1247date12_30_2000.flv

but am unsure how to use this to then create a date object

[Dan_Bracuk]

You don't use regex to create a date object.  You use it to extract the bits of the string you want.  Then you use parsedatetime or createdate to create the date object from that string.

[nikos101]

How do you capture the dates?

I tried this:

<cfset test = reFind(".*date(\d{2})_(\d{2})_\d{4}","staffID1247date12_30_2000.flv",0,true)>

but all I get is len and pos arrays

[Adam Cameron.]

OK, well what do those len and pos arrays represent?  Have you read the docs?

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec22c24-7e94.html

But anyway, you don't need to find & extract, you can simply replace the bits you don't want with nothing, and reorder the bits you do want into a string which parseDateTime() will accept.

--

Adam

[nikos101]

lol I'm so used to capturing stuff with flex and php, this length and pos stuff is not cool

[nikos101]

I tried this, but its capturing the year as staffID1247date2000_12_30

     <cfset fileName = "staffID1247date2000_12_30.flv">
          <cfset test = reFind(".*date(\d{4})_(\d{2})_\d{2}",fileName,0,true)>
          <cfset year =   mid(fileName, test.pos[1], test.len[1])>
          <cfset month =   mid(fileName, test.pos[2], test.len[2])>
          <cfset day =   mid(fileName, test.pos[3], test.len[3])>

          <cfset date =   parseDateTime(year + ' ' + month + ' ' + day)>

[nikos101]

Ok, I think I've got it

Element one of the pos

array contains the starting index in the search string of the string that matched the regular expression.

But in my example I find that the day  , the 3rd () is not captured

[nikos101]

oh, I need to add the 3rd ()

[Dan_Bracuk]

Another approach is to use rereplace to get rid of everything that is before and after the date string.  What's left is an underscore delimited list and you can use the list elements with CreateDate().  That way you don't have to manipulate the string after you've found it.

[nikos101]

I'm trying to join here:

<cfloop query="fileQuery"  >

<cfset CountVar = CountVar + 1>

  <cfif Type eq 'File'>
    <cfset fileName = "staffID1247date2000_12_30.flv">
    <cfset fileName = Name>
    <cfset test = reFind(".*date(\d{4})_(\d{2})_(\d{2})",fileName,0,true)>
    <cfset year =   mid(fileName, test.pos[2], test.len[2])>
    <cfset month =   mid(fileName, test.pos[3], test.len[3])>
    <cfset day =   mid(fileName, test.pos[4], test.len[4])>
    <cfset date = parseDateTime(year & ' ' & month & ' ' & day)>
    <cfset Temp = QuerySetCell(fileQuery, "parseDate", date,CountVar)>
  </cfif>
</cfloop

<cfquery name= final dbtype="query" >
  select * from sp, fileQuery
   where  fileQuery.parseDate = DATECASHIERWASTESTED
  </cfquery>

I expect this to match but it doesn't, I also check in debugger

fileQuery.parseDate = 2000-07-02

DATECASHIERWASTESTED = 2010-07-02 00:00:00.0

any ideas friends?

[Adam Cameron.]

I expect this to match but it doesn't, I also check in debugger

fileQuery.parseDate = 2000-07-02

DATECASHIERWASTESTED = 2010-07-02 00:00:00.0

Why would you expect those two dates to match?  They're ten years different from each other 😉

--

Adam

[nikos101]

hmm

Fri Jul 2 00:00:00 GMT+0100 2010   
is been turned into 2000_07_02

by

DateFormat(FORM.date, 'yyyy_mm_dd')

so sad

[ilssac · Accepted solution]

That's because dateFormat() expects a date object

Fri Jul 2 00:00:00 GMT+0100 2010 is a date string

ColdFusion functions can sometimes figure out a date string and automagically make a date object out of one when it needs to.  But it is far from fool proof.

It is a better pratcice not to rely on this and use the parseDate() or the more explict createDate() when necessary.

[nikos101]

for some reason the blue code works fine but the red has no effect the staffID is 877 but no value is assigned to the cell:

<cfset  number = QueryAddColumn(fileQuery, "parseDate", "Date",arrayNew(1))>
<cfset  number = QueryAddColumn(fileQuery, "staffID", "Integer",arrayNew(1))>

<cfset CountVar = 0>

<cfloop query="fileQuery"  >

<cfset CountVar = CountVar + 1>

  <cfif Type eq 'File'>
   <cfset fileName = Name>
    <cfset test = reFind("staffID([0-9]+)date(\d{4})-(\d{2})-(\d{2})",fileName,0,true)>
    <cfset staffID =   mid(fileName, test.pos[2], test.len[2])>
    <cfset year =   mid(fileName, test.pos[3], test.len[3])>
    <cfset month =   mid(fileName, test.pos[4], test.len[4])>
    <cfset day =   mid(fileName, test.pos[5], test.len[5])>
    <cfset date = parseDateTime(year & ' ' & month & ' ' & day)>
    <cfset Temp = QuerySetCell(fileQuery, "parseDate", date,CountVar)>
    <cfset Temp2 = QuerySetCell(fileQuery, "staffID", staffID,CountVar)>
  </cfif>
</cfloop>

[Dan_Bracuk]

Are you sure the staffid variable is what you think it is?  Did you output or dump it after your cfset tag?

[nikos101]

so strange if I do

<cfdump var="#staffID#">

I get an [empty string]

but

    <cfdump var="#mid(fileName, test.pos[2], test.len[2])#">

works fine

[Dan_Bracuk]

You are probably confusing cold fusion because your column name is the same as your variable name.  Try changing one of them, or, if you enjoy typing, specify the variables scope where necessary.