Tuesday 26 June 2007

Sort by date greater than other date

Often XML contains dates in all sorts of formats, and either XSL Developers have to go through a great deal of diplomatic negotiations with Developers responsible for the XML, or worse there is no one to talk to.

Ultimately the job needs to get done, and as XSL 1.0 at least does not recognise the Date data type natively, it’s back to hacking away the xsl:sort element so the sorting can take place.

As XSL 1.0 Processors understand number sorting, dates can easily and meaningfully be sorted in the format yyyymmdd, which represents a meaningful numeric value for any given date, maintaining the correct order once sorted. This means that no matter what format you get dates in xml as soon as you manage to convert the date to yyyymmdd you can sort by Date.

Lets say you have some date in the traditional US format mm-dd-yyyy (10-05-1971). To format this date you know that all the characters before the first occurrence of the hyphen character represent the month, so you can easily get those with substring-before([dateNode], ‘-‘). To get the day, you can use the same technique twice, but this time using substring-before and substring-after combined, substring-before(substring-after([dateNode], ‘-‘), ‘-‘) .

To get year, you guessed . . . substring-after(substring-after([dateNode], ‘-‘), ‘-‘)

This technique works for the following formats:
mm-dd-yyyy
dd-mm-yyyy
d-m-yyyy
d-m-yy

If you knew the exact character length of your date you could use the substring() function instead:
Month – substring([dateNode], 1, 2)
Day – substring([dateNode], 4, 2)
Year – substring([dateNode], 7, 4)

Whatever technique you use, all you need to do is to concatenate the string fragmentation results of your date string to yyyymmdd, or equivalent, like:
concat(
substring-after(substring-after([dateNode], ‘-‘), ‘-‘),
substring-before([dateNode],
substring-before(substring-after([dateNode], ‘-‘)
)

You can now use the concatenated string inside an XPath predicate.

The following example creates a list of news where the date is greater than the expiry date and sorts the results by date descending.


XML


<PICNews>
<news>
<Date>6/31/2007</Date>
<Expires>6/30/2007</Expires>
<Author>Dan Jurden</Author>
<Type>Memo</Type>
<Text>This is a memo.</Text>
</news>
<news>
<Date>7/21/2007</Date>
<Expires>6/30/2007</Expires>
<Author>Dan Jurden</Author>
<Type>News</Type>
<Text>This is some news.</Text>
</news>
<news>
<Date>6/22/2007</Date>
<Expires>6/23/2007</Expires>
<Author>Dan Jurden</Author>
<Type>Alert</Type>
<Text>This is an alert.</Text>
</news>
</PICNews>



XSL

<xsl:template match="/">
<body>
<h2>PIC Information</h2>
<div id="news">
<xsl:apply-templates select="
PICNews/news[
concat(
substring(Date, (string-length(Date) - 3), string-length(Date)),
substring-before(Date, '/'),
substring-before(substring-after(Date, '/'), '/')
)

>

concat(
substring(Expires, (string-length(Expires) - 3), string-length(Expires)),
substring-before(Expires, '/'),
substring-before(substring-after(Expires, '/'), '/')
)

]
">

<xsl:sort select="
Date[
concat(
substring(Date, (string-length(Date) - 3), string-length(Date)),
substring-before(Date, '/'),
substring-before(substring-after(Date, '/'), '/')
)
]
"
order="descending"/>
</xsl:apply-templates>
</div>
</body>
</xsl:template>


<xsl:template match="news">
<div class="newsItem">
<img src="images/{translate(
Type,
'QWERTYUIOPASDFGHJKLZXCVBNM',
'qwertyuiopasdfghjklzxcvbnm'
)
}.jpg"
width="32px"/>
<p class="date">
<xsl:text>Date: </xsl:text>
<xsl:value-of select="Date"/>
</p>
<p class="news-text">
<xsl:value-of select="Text"/>
</p>
</div>
</xsl:template>

4 comments:

Anonymous said...

Thank you! I'd been beating my head against a wall trying to get conditional date formatting into a content query xslt. Your post got me going in the right direction.

Miguel de Melo said...

glad it was usefull

Unknown said...

This was really helpful, thank you

Brandt said...

Awesome! Thanks