• Skip to main content
  • Skip to primary sidebar

Technical Notes Of
Ehi Kioya

Technical Notes Of Ehi Kioya

  • About
  • Contact
MENUMENU
  • Blog Home
  • AWS, Azure, Cloud
  • Backend (Server-Side)
  • Frontend (Client-Side)
  • SharePoint
  • Tools & Resources
    • CM/IN Ruler
    • URL Decoder
    • Text Hasher
    • Word Count
    • IP Lookup
  • Linux & Servers
  • Zero Code Tech
  • WordPress
  • Musings
  • More
    Categories
    • Cloud
    • Server-Side
    • Front-End
    • SharePoint
    • Tools
    • Linux
    • Zero Code
    • WordPress
    • Musings
Home » Backend (Server-Side) » Sorting Date And Time In XSL

Sorting Date And Time In XSL

By Ehi Kioya 3 Comments

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.

Found this article valuable? Want to show your appreciation? Here are some options:

  1. Spread the word! Use these buttons to share this link on your favorite social media sites.
  2. Help me share this on . . .

    • Facebook
    • Twitter
    • LinkedIn
    • Reddit
    • Tumblr
    • Pinterest
    • Pocket
    • Telegram
    • WhatsApp
    • Skype
  3. Sign up to join my audience and receive email notifications when I publish new content.
  4. Contribute by adding a comment using the comments section below.
  5. Follow me on Twitter, LinkedIn, and Facebook.

Related

Filed Under: Backend (Server-Side), XML Tagged With: XML, XSL, XSLT

About Ehi Kioya

I am a Toronto-based Software Engineer. I run this website as part hobby and part business.

To share your thoughts or get help with any of my posts, please drop a comment at the appropriate link.

You can contact me using the form on this page. I'm also on Twitter, LinkedIn, and Facebook.

Reader Interactions

Comments

  1. Jesse Alama says

    April 24, 2015 at 2:43 am

    Nice! It is indeed frustrating at times to work just with XSLT 1.0. Even if one has XSLT 2.0 or 3.0 available, one might have to use this kind of solution anyway, because (as you say) who knows whether the XML you’re working with has dates represented in the way that XSLT expects…

    Reply
    • Ehi Kioya says

      April 24, 2015 at 5:01 am

      True. I have found these two solutions useful too many times! Thanks for your comment.

      Reply
  2. Paul says

    March 30, 2019 at 6:10 am

    How about this format?
    2016-06-10T01:38:13.687+02:00 vs
    2016-06-09T23:59:13.687-05:00 .

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

23,734
Followers
Follow
30,000
Connections
Connect
14,568
Page Fans
Like
  • Recently   Popular   Posts   &   Pages
  • Actual Size Online Ruler Actual Size Online Ruler
    I created this page to measure your screen resolution and produce an online ruler of actual size. It's powered with JavaScript and HTML5.
  • How To Change A SharePoint List Or Library URL How To Change A SharePoint List Or Library URL
    All versions of the SharePoint user interface provide an option to change the title (or display name) of a list or library. Changing SharePoint library URL (or internal name), however, is not exactly very intuitive. We will discuss the process in this article.
  • WordPress Password Hash Generator WordPress Password Hash Generator
    With this WordPress Password Hash Generator, you can convert a password to its hash, and then set a new password directly in the database.
  • About
  • Contact

© 2022   ·   Ehi Kioya   ·   All Rights Reserved
Privacy Policy