RE: [xsl] XSL:sort dates as numbers gets incorrect results

["Michael Kay" <mike@xxxxxxxxxxxx>]

I would tackle this by first transforming the document to use ISO date/time
formats, then sorting, then (if you really must) converting back into the
peculiar US format.

Michael Kay
http://www.saxonica.com/


> -----Original Message-----
> From: Ninad Tambe [mailto:ninadtambe@xxxxxxxxx] 
> Sent: 28 October 2005 20:25
> To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [xsl] XSL:sort dates as numbers gets incorrect results
> 
> I have an XML document that have about 200 nodes that
> look roughly like this:
> 
> <root>
> <news>
> <industry>Aerospace</industry>
> <lastupdated>7/28/2005 12:10:00 PM</lastupdated>
> <title>News from aerospace</title>
> </news>
> <news>
> <industry>Mechanical</industry>
> <lastupdated>7/28/2005 12:23:00 AM</lastupdated>
> <title>News from mechanical</title>
> </news>
> <news>
> <industry>Aerospace</industry>
> <lastupdated>7/28/2005 9:12:00 PM</lastupdated>
> <title>News from aerospace</title>
> </news>
> <news>
> <industry>Electrical</industry>
> <lastupdated>7/28/2005 6:01:00 AM</lastupdated>
> <title>News from electrical</title>
> </news>
> <news>
> <industry>Computer Science</industry>
> <lastupdated>3/28/2002 12:01:00 AM</lastupdated>
> <title>News from CS</title>
> </news>
> <news>
> <industry>Aerospace</industry>
> <lastupdated>4/28/2005 12:01:00 AM</lastupdated>
> <title>News from aerospace</title>
> </news>
> </root>
> 
> I need to display top 3 items in reverse chronological
> order that have either Aerospace or Electrical as
> industry. I need the first 2 (assuming they are sorted
> by lastupdated).
> 
> I need to sort these nodes where industry is Aerospace
> or Electrical (for example), sort them by lastupdated
> (reverse chronological). I am able to do that so far
> successfully. Its just that I need to display the top
> 2 items (of the 3 or more that are selected). How do I
> accomplish that? I cannot use the position(), because
> it relates to the nodes in the document, not the
> position of nodes after sort.
> 
> Here is the XSL:
> 
> <?xml version='1.0'?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
> 
> <xsl:template match="/">
> <table>
> <xsl:for-each select="root/news[industry/text() =
> 'Aerospace' or
> industry/text() = 'Electrical']
> "> 
> 
> <!-- lastupdated is MM/DD/YYYY HH:MI:SS XM e.g.
> 7/27/2005 4:12:22 PM -->
> <xsl:sort order="descending" data-type="number"
> select='substring-after(substring-after(substring-before(LASTUPDATED,
> " "), "/"), "/")'></xsl:sort>
> <!-- sort the yyyy as number -->
> <xsl:sort order="descending" data-type="number"
> select='substring-before(substring-before(LASTUPDATED,
> " "), "/")'></xsl:sort>
> <!-- sort MM as number -->
> <xsl:sort order="descending" data-type="number"
> select='substring-after(substring-before(LASTUPDATED,
> " "), "/")'></xsl:sort>
> <!-- sort date as number -->
> <xsl:sort order="descending"
> select='substring-after(substring-after(LASTUPDATED, "
> "), " ")'></xsl:sort>
> <!-- within day, sort XM (AM/PM) as string, PM first
> -->
> <xsl:sort order="descending" data-type="number"
> select='substring-before(substring-after(LASTUPDATED,
> " "), ":")'></xsl:sort>
> <!-- sort the HH as number -->
> <xsl:sort order="descending" data-type="number"
> select='substring-before(substring-after(substring-after(LASTUPDATED,
> " "), ":"), ":")'></xsl:sort>
> <!-- sort the MM as number --> 
> <!-- The one problem currently with this approach is
> that 12:20 AM will be considered latest when compared
> to 9:20 AM -->
> 
> <xsl:if test="position() &lt; 4">
> 
> 		<tr><td>
> 			<xsl:value-of
> select='concat(substring-before(substring-after(lastupdated,
> " "), ":"), ":",
> substring-before(substring-after(substring-after(lastupdated,
> " "), ":"), ":"), " ",
> substring-after(substring-after(lastupdated, " "), "
> "), " ET, ", substring-before(lastupdated, " "))' /> 
> 			
> 		</td></tr>
> 
> <tr><td>
> <xsl:value-of select="title"/>
> </td></tr>
> </xsl:if> 
> 
> </xsl:for-each>
> </table>
> </xsl:template>
> 
> </xsl:stylesheet>
> 
> 
> I get the results fine, but the problem I have is that
> 12:10:00 PM comes before 9:12:00 PM, which is
> incorrect (reverse chronological). Same behavior for
> 12:23:00 AM displaying before 6:01:00 AM.
> 
> Please help.
> 
> 
> 
> 
> 	
> 		
> __________________________________ 
> Yahoo! Mail - PC Magazine Editors' Choice 2005 
> http://mail.yahoo.com