Recently, I came across the need to sort the output of some XML transformed via XSLT. Normally, this isn’t too difficult. We just perform the sort within our template with some XSL code as simple as:

<xsl:sort select="ColumnToSortOn" order="ascending" type="text" />

This approach works well until you need to sort by a date value. It gets even worse if the date value includes a time. In my case, the values I needed to sort were in this format “14/03/2013 6:16:37 AM”.

Since XML often contains dates in all sorts of formats, XSL developers usually have to go through a great deal of diplomatic negotiations with the developers responsible for the XML. In my case however, there was no one to talk to. The job just had to be done.

Note: The ISO date format yyyy-mm-dd is supported directly in XSLT 2.0 via the data type xs:date. This post assumes that you’re stuck with XSLT 1.0 or a different date format.

I will present two methods for sorting by dates. Full date and time sort (for this format “14/03/2013 6:16:37 AM”) is presented in Method 2.
 

METHOD 1: XSL Sort For Dates Only

This method assumes that you have access to the original XML. It is good for dates only.

Say your original XML looks like:

<Root>
  <Element Created="26 January 2003"/>
  <Element Created="28 Feburary 1979"/>
  <Element Created="3 November 1989"/>
  <Element Created="1 July 2003"/>
  <Element Created="26 January 1977"/>
</Root>

You can sort it by having one more attribute for each Element node which will have the following format “yyyymmdd”.
For example: If the date is 26 January 2003, then the format will be “20030126”. 2003 is the year, 01 is the month and finally 26 is the date. So let’s add one more attribute called SortDate. Our above XML will now look like:

<Root>
  <Element Created="26 January 2003" SortDate="20030126"/>
  <Element Created="28 Feburary 1979" SortDate="19790228"/>
  <Element Created="3 November 1989" SortDate="19891103"/>
  <Element Created="1 July 2003" SortDate="20030701"/>
  <Element Created="26 January 1977" SortDate="19770126"/>
</Root>

Note: Month name and day which are less than 10 should be denoted by two numeric values, otherwise it will affect the sort order, resulting in wrong results. For example: January should be denoted by “01”.

Now sort the XML data with respect to SortDate attribute using numerical data. The sort code will be:

<xsl:sort select="@SortDate" data-type="numeric" order="descending"/>

 

METHOD 2: XSL Sort For Date And Time

In this case, we DO NOT have access to the original XML. However, we know that the data format will always be like this “14/03/2013 6:16:37 AM”.

Here’s the sort method:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml" >
  <xsl:output indent="no" method="html" />
  <xsl:template match="/">
	  <xsl:for-each select="Root/Element" >
		<!-- Date looks like this: 14/03/2013 6:16:37 AM -->

		<!-- year part -->
		<xsl:sort select="substring-after(substring-after(substring-before(Column[@fInternalName = 'Created']/@Value,' '),'/'), '/')" data-type="number" order="descending"/>

		<!-- month part -->
		<xsl:sort select="substring-before(substring-after(substring-before(Column[@fInternalName = 'Created']/@Value,' '),'/'), '/')" data-type="number" order="descending"/>

		<!-- day part -->
		<xsl:sort select="substring-before(Column[@fInternalName = 'Created']/@Value, '/')" data-type="number" order="descending"/>

		<!-- am/pm part. Note: No data type specified -->
		<xsl:sort select="substring-after(substring-after(Column[@fInternalName = 'Created']/@Value,' '),' ')" order="descending"/>

		<!-- hour part -->
                <!-- Note the mod function to convert 12AM and 12PM to 0 -->
		<xsl:sort select="substring-before(substring-after(Column[@fInternalName = 'Created']/@Value,' '), ':') mod 12" data-type="number" order="descending"/>

		<!-- minute part -->
		<xsl:sort select="substring-before(substring-after(Column[@fInternalName = 'Created']/@Value,':'),':')" data-type="number" order="descending"/>

		<!-- second part -->
		<xsl:sort select="substring-before(substring-after(substring-after(substring-after(Column[@fInternalName = 'Created']/@Value,' '),':'), ':'), ' ')" data-type="number" order="descending"/>
	  </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

The idea is to extract the year part of the date and sort the date values using year, then extract the month part of the date and sort the date values using month and so forth. We even have to sort with AM and PM. The last bit of sorting is with the second part of the time value.