openbd cfloop over a date

Question

I'm trying to convert my sites from CF8 to openBD. I have a cfloop in a site that loops over a date range.

In essence, I want to insert a new record into the db for every 2 weeks (step) of a date range (from and to)

my loop looks like this...

<cfloop 
  from  = "#form.startDate#" 
  to    = "#form.endDate#" 
  index = "i" 
  step  = "#theStep#"
>

This works perfectly in CF8, in openBD, I get this error... Data not supported: value [11/05/09] is not a number

Any ideas of a work around?

Thx

Answer 1

I can't see your code, but here's my first suggestion:

<cfset current = [your begin date]>
<cfloop condition = "datecompare(enddate, current)">
  [do stuff]
  <cfset current = dateadd('d', 14, current)>
</cfloop>

HTH.

Answer 2

As Ben says, your code isn't there - you need to use the 101 010 icon to create a code block for it.

Here's another solution which should work:

<cfloop index="Current" from="#parseDateTime(StartDate)#" to="#parseDateTime(EndDate)#" step="14">
    [do stuff]
</cfloop>

Answer 3

Your problem lies in not checking for ambiguous locale dependent date strings from your FORM.

A more robust version would be this:

<cfset SetLocale("English (US)")> <!--- set expected input locale here --->

<cfif LSIsDate(form.startDate) and LSIsDate(form.endDate)>
  <cfset theStep = 14>

  <cfloop 
    from  = "#LSParseDate(form.startDate)#" 
    to    = "#LSParseDate(form.endDate)#" 
    index = "i" 
    step  = "#theStep#"
  >
    <!--- do stuff --->
  </cfloop>
<cfelse>
  <!--- output some error message --->
</cfif>

It would be helpful to restrict people to entering unambiguous date formats into the FORM, like "yyyy-mm-dd".

The "value is not a number" error comes from the fact that the loop still goes over numbers, even if you feed it dates. It uses a numerical representation of these dates then, but they must be valid and intelligible for that to work.