Using an array to sort dates.

{ Posted By : Eric Cobb on October 7, 2010 }
Related Categories: Tips 'n Tricks, CFML, ColdFusion

Here's an interesting little tidbit I thought I'd pass on. Yesterday I had a case where I needed to select the latest date out of about 4-5 given dates. The dates could range anywhere from today to a year ago. My first inclination was to just have a bunch of CFIFs comparing the dates and filtering through and determine which one was the latest. But then, I thought about how easily arrays can sort values and wondered if I could apply that to dates as well. As it turns out, you can, and it's really simple to do.

Here's how to do it:
First, let's create an array with some random dates it in:

<cfset variables.myArr = ArrayNew(1)>
<cfset variables.myArr[1] = DateFormat(DateAdd("d",-25,now()),'short')>
<cfset variables.myArr[2] = DateFormat(DateAdd("d",-5,now()),'short')>
<cfset variables.myArr[3] = DateFormat(DateAdd("d",0,now()),'short')>
<cfset variables.myArr[4] = DateFormat(DateAdd("d",-2,now()),'short')>
<cfset variables.myArr[5] = DateFormat(DateAdd("d",-125,now()),'short')>
<!--- Dump the array to see our values. --->
<cfdump var="#variables.myArr#">

When we dump the array, we see all of our dates in the order they were assigned:
date array 1

Now, to sort the dates in descending order, we simply use ArraySort():

<cfset ArraySort(variables.myArr,"numeric","desc")>
<!--- Dump the newly sorted array to see our values. --->
<cfdump var="#variables.myArr#">
As you can see now, our array has been resorted so that the dates are now in descending order, with the latest date being the first element in the array.
date array 2

Now that our dates are sorted, we know we can get the latest date just by selecting the first array element:

The latest date is <cfoutput>#variables.myArr[1]#</cfoutput>.

Likewise, if we wanted to get the earliest date, just sort your array in ascending order instead, and the earliest date would then be the first element in the array. Or, if you wanted to get fancy with it and get the first and last date at one time without resorting the array:

The latest date is <cfoutput>#variables.myArr[1]#</cfoutput>.<br />
The earliest date is <cfoutput>#variables.myArr[ArrayLen(variables.myArr)]#</cfoutput>

How does it work?
If you'll notice, in our ArraySort() function we're sorting our array numerically. Looking at the dates in our dumps above, since they're nicely formatted with the slashes, you would think that the dates would be considered strings, right? I mean, "/" is not a valid number. But, ColdFusion is smarter than that. If you were to check any of the dates in our array with IsNumericDate() it would return true. This is because ColdFusion automatically converts valid date string formats to date/time objects, which are actually real numbers. I'm not sure, but I think ColdFusion turns the date into a real number by calculating the serial date, which is the number of days from 0/1/1900. At any rate, once the date has been converted to a real number, ColdFusion can then sort it numerically, and that's how our dates get sorted in the array.

But it doesn't work across the board.
Interestingly enough, when I run the above code samples on Railo, it DOES NOT sort the dates numerically. In fact, it doesn't do anything. Both dumps above would be identical, Railo just seems to ignore the ArraySort() function in this particular case. I've asked about this on the Railo Google Group, so hopefully they can tell us why this is. (I haven't tested on Open Blue Dragon, but I would be interested to see the results if anyone out there can test it for me.) I have to admit, I'm really not surprised it doesn't work in Railo. (no slight against Railo!) As I pointed out in my Discrepancies in the way ColdFusion validates dates post, ColdFusion doesn't seem to be consistent in the way it views dates throughout various functions. So, it would stand to reason that its way of viewing the dates in the ArraySort() function may not be considered the norm.

At any rate, this is a neat little trick that I'm definitely going to add to my arsenal. Some of the logic behind how/why it works may be a little fuzzy, but since I do a ton of works with dates this one is going to come in handy!