Skip to main content
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Brussels) with: Ray Camden and Alison Huselid and David Huselid
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Brussels) with: Ray Camden ( @cfjedimaster ) Alison Huselid David Huselid

Using CFLoop To Loop Over ColdFusion Dates

By on
Tags:

This morning, I got to share the magic of using CFLoop to easily loop over ColdFusion dates. A co-worker of mine was having trouble and resorted to using an index-loop and the DateAdd() function to step through a date span one day at a time:

<!--- Set default start date. --->
<cfset dtStart = Now() />

<!--- Set default iteration date. --->
<cfset dtToday = dtStart />

<!--- Loop over the number of days. --->
<cfloop index="intDayOffset" from="0" to="6" step="1">

	<!--- Create the appropriate offset date. --->
	<cfset dtToday = DateAdd( "d", intDayOffset, dtStart ) />

	Today is: #dtToday#<br />

</cfloop>

Does that look all to familiar to you? It does to a lot of people (before today!). Luckily, I overheard his conversation and was able to step in.

Before getting into this, remember that ColdFusion, like SQL, can view dates as the amount of time that has passed since a given start date (as determined by ColdFusion to be the earliest date available). This number is a floating point number where the integer portion is days and the decimal portion is fractions of a day. I am not sure how dates are "stored" internally to the ?Java? date object underneath, but ColdFusion will automatically perform the cast to the floating point number when required.

So, let's take a look at the loop:

<cfloop
	index="dtToday"
	from="#dtStart#"
	to="#dtEnd#"
	step="#CreateTimeSpan( 1, 0, 0, 0 )#">

	The numeric value of today is: #dtToday#

	The "friendly" value of today is: #DateFormat( dtToday, "mmm d, yyyy" )#
</cfloop>

Let's look at this a piece at a time, staring with the tag attributes. The INDEX field is standard to all loops except Collection loops. This keeps track of the index value during each CFLoop iteration.

The FROM and TO fields contain the boundary dates. Now, remember how I said that ColdFusion will take care of the type cast automatically? That is what is happening here. For the FROM and TO fields, ColdFusion is automatically casting the dates to a numeric, floating-point value (ex. 34354.454345).

The STEP field determines the increment we will be making for each CFLoop iteration. We want to make a date-time increment, but, since our CFLoop is really dealing with the numeric representation of dates, our increment needs to be a numeric value. That is what CreateTimeSpan() does: it represents a time span in terms of the numeric equivalent:

CreateTimeSpan(
	1, <!--- Days. --->
	0, <!--- Hours. --->
	0, <!--- Minutes. --->
	0  <!--- Seconds. --->
	)

To help illustrate this idea, here are some common CreateTimeSpan() calls:

<!--- 1 Day: [ 1 ] --->
CreateTimeSpan( 1, 0, 0, 0 )

<!--- 1 Hour: [ 0.0416666666667 ] --->
CreateTimeSpan( 0, 1, 0, 0 )

<!--- 20 Minutes: [ 0.0138888888889 ] --->
CreateTimeSpan( 0, 0, 20, 0 )

With some quick math, you will see that since there are 24 hours in a day, one hour is 1/24 of the day value. Now, if you really know your ColdFusion tag default values, you will know that the STEP attribute defaults to 1, which means that if you leave out the STEP from a ColdFusion date/time loop, it will increment one day at a time.

But, to continue with the explanation, let's now take a look at what's inside the CFLoop. As stated before, dtToday contains the date value for each iteration. Because we have converted all of our dates to numbers, the value of dtToday is also numeric. That's why the first line:

The numeric value of today is: #dtToday#

... will display a value like 38911.6035301. We can, however, treat this as if it were a date and ColdFusion will perform the cast automatically. That is why, this line:

The "friendly" value of today is: #DateFormat( dtToday, "mmm d, yyyy" )#

... will display a value like "Jul 13, 2006."

One last thing to note is that while we are only displaying the date portion, the time values are also carried across CFLoop iterations. If your start date/time object has, for time, 2:00 PM, each dtToday value will have a different date, but that date will also be 2:00 PM.

Understanding this has not revolutionized the way I loop over dates, but it has certainly made my code for things such as event calendars 100 times easier to write. If you are interested in how this related to SQL, including date/time comparison, please check out my SQL date comparison post and my SQL time comparison post.

Want to use code from this post? Check out the license.

Reader Comments

1 Comments

Wow! Just wanted to let you know that this is a great solution. My code looked exactly like the first block and was starting to get sloppy. Thanks for posting this.

1 Comments

I notice something interesting about this code, at least in CF7. If you are looping from say 9:00am to 1:00pm on a hourly basis, you might expect the output to be:

9:00 AM
10:00 AM
11:00 AM
12:00 PM
1:00 PM

In fact, the loop will stop at 12:00 PM.

To get around this I add the 'interval' (in my case in minutes) to my end time.

<cfset LoopEndTime = DateAdd("n",TimeInterval,EndTime)>
<cfloop from="#StartTime#" to="#LoopEndTime#"
index="i" step="#CreateTimeSpan(0,0,TimeInterval,0)#">
#TimeFormat(i,"h:mm tt")#<br>
</cfloop>

15,640 Comments

@Kathy,

I am not sure why your example isn't working. I think you are making it too complicated. You have to simplify the way you are doing things. CFLooping in ColdFusion is inclusive, meaning it will include the start and end values (not stop before the end value). Look at this example:

<cfset dtStart = "9:00 AM" />
<cfset dtEnd = "1:00 PM" />

<cfloop
index="dtHour"
from="#dtStart#"
to="#dtEnd#"
step="#(1/24)#">

#TimeFormat( dtHour, "h:mm TT" )#<br />

</cfloop>

Here, my STEP interval is one hour (1/24th of a Day). The start time is 9AM and the end time is 1PM. Running the above code, I get:

9:00 AM
10:00 AM
11:00 AM
12:00 PM
1:00 PM

My guess is that you are trying to make your Step attribute value too complicated. Maybe it wasn't actually an hour?

92 Comments

Ben...

A comment, and a question.

1) In your code sample, you reference a variable called dtEnd, but never create it, nor indicate it's value.

2) What might this code like if I wanted to loop over months, rather than days? Say I wanted to show the last 3 months (including the current month), from left to right?

15,640 Comments

@Andy,

As for #1, you are totally right. That was an oversight. Not sure how that didn't make it into the code. It was definitely there as I always run the code to make sure that it works. Hmmm. Regardless, dtEnd was simply a date/time value.

As for #2, looping over months cannot quite be as elegant since months are not set amounts of time. That is why I created a ColdFusion custom tag that allows easy, more obvious looping over dates. Feel free check out my cf_dateloop entry:

www.bennadel.com/index.cfm?dax=blog:893.view

In that case, all you have to do is provide the "m" DatePart in the loop:

<cf_dateloop
index="dtDay"
from="#dtStart#"
to="#dtEnd#"
datepart="m"
step="1">

--- Loop over 1 (step) month (m) at a time ---

</cfloop>

15,640 Comments

@Andy,

The DateAdd() method works fine. In fact, I believe it will always return a date/time value, so at least you don't have to deal with numeric dates.

2 Comments

This method also works well and is similar to your co-workers method.

<pre>
startDate = CreateDate(2000, 1, 1);
endDate = CreateDate(2009, 10, 1);

for (ii = startDate; DateCompare(ii, endDate) <= 0; ii = DateAdd('yyyy', 1, ii))
{
WriteOutput(
DateFormat(ii, "mmm d, yyyy")
);
}
</pre>

1 Comments

To follow up on Everett's idea, of course it's using cfscript which limits what you can do inside the loop, right? Wrong...just write a cffunction and call it from within the loop like so:

<cfscript>
...etc...
for(ii = startDate; DateCompare(ii, endDate) <=0; ii = DateAdd('yyyy',1,ii))
{
runMyCfTagBasedCode(ii);
}
</cfscript>
<cffunction name="runMyCfTagBasedCode">
<cfargument name="ii">
[do my tag based stuff with the loop integer here]
</cffunction>

2 Comments

Once again, you are the bacon saver of the day. Using this as a starting point saved me HOURS of fiddling around :)

Thanks!

15,640 Comments

@Jack,

Awesome - using date/time objects to do looping and math is just super great in ColdFusion. One thing to be aware of since this was posted, Ray Camden found a very curious behavior regarding the implicit numeric conversions:

www.bennadel.com/blog/1615-ColdFusion-CreateTimeSpan-And-CFLoop-via-Ray-Camden-.htm

It only happens on the outliers of the loop; but, you can use createTimeSpan() to get around this. I don't think I've ever actually run into that problem in "the wild." But, it's good to be aware of in case you get some odd behavior.

1 Comments

I really liked the way you presented the answer. As soon as started reading I was like "whats so different from what i wrote".. You rock Ben!!

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel