Convert date time format in XSLT

Question

I have a date value in a field in XML file in this format:

<Date value="4/1/2013 5:13:41 PM"/>

I want to convert it into a standard XSD format:

2013-04-01T17:13:41.000Z

How can I do that in my XSL transform? I can use both 1.0 and 2.0 stylesheet versions.

Answer 1

So...I was bored and hadn't played with xsl:analyze-string before. Here's a regex-based solution :

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="2.0">

  <xsl:template match="/item/date">
    <xsl:analyze-string select="@value" regex="([0-9]+)/([0-9]+)/([0-9]+) ([0-9]+):([0-9]+):([0-9]+) (PM|AM)">

      <xsl:matching-substring>
        <xsl:variable name="month" select="number(regex-group(1))"/>
        <xsl:variable name="day" select="number(regex-group(2))"/>
        <xsl:variable name="year" select="number(regex-group(3))"/>
        <xsl:variable name="hours">
          <xsl:choose>
            <xsl:when test="regex-group(7) = 'PM'">
              <xsl:value-of select="12 + number(regex-group(4))"/>
            </xsl:when>
            <xsl:otherwise><xsl:value-of select="number(regex-group(4))"/></xsl:otherwise>
          </xsl:choose>
        </xsl:variable>
        <xsl:variable name="minutes" select="number(regex-group(5))"/>
        <xsl:variable name="seconds" select="number(regex-group(6))"/>
        <xsl:variable name="dateTime" select="xs:dateTime( concat($year, '-', format-number($month, '00'), '-', format-number($day, '00'), 'T', format-number($hours, '00'), ':', format-number($minutes, '00'), ':', format-number($seconds, '00'), 'Z')  )" />

        <reformattedDate>
          <xsl:value-of select="$dateTime"/>
        </reformattedDate>
      </xsl:matching-substring>
    </xsl:analyze-string>
  </xsl:template>
</xsl:stylesheet>

I ran this against a test xml file like this :

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<item>
    <date value="4/1/2013 5:13:41 PM"/>
</item>

And the output is this :

<?xml version="1.0" encoding="UTF-8"?>
    <reformattedDate xmlns:xs="http://www.w3.org/2001/XMLSchema">2013-04-01T17:13:41Z</reformattedDate>

If you want to format the output more precisely, as already recommended, you can use the format-date function.

Answer 2

Here is an XSLT 1.0 version, using substring-before and substring-after.

Thanks to adhocgeek for the XML input.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="/item">
    <xsl:copy>
      <xsl:apply-templates select="date"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="date">
    <xsl:copy>

      <xsl:variable name="date" select="substring-before(@value, ' ')"/>
      <xsl:variable name="M" select="substring-before($date, '/')"/>
      <xsl:variable name="D-Y" select="substring-after($date, '/')"/>
      <xsl:variable name="D" select="substring-before($D-Y, '/')"/>
      <xsl:variable name="Y" select="substring-after($D-Y, '/')"/>

      <xsl:variable name="time-ampm" select="substring-after(@value, ' ')"/>
      <xsl:variable name="time" select="substring-before($time-ampm, ' ')"/>
      <xsl:variable name="ampm" select="substring-after($time-ampm, ' ')"/>
      <xsl:variable name="h" select="substring-before($time, ':')"/>
      <xsl:variable name="m-s" select="substring-after($time, ':')"/>
      <xsl:variable name="m" select="substring-before($m-s, ':')"/>
      <xsl:variable name="s" select="substring-after($m-s, ':')"/>

      <xsl:variable name="hh">
        <xsl:choose>
          <xsl:when test="$ampm = 'PM'">
            <xsl:value-of select="format-number($h + 12, '00')"/>
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="format-number($h, '00')"/>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:variable>          

      <xsl:value-of select="concat($Y, '-', $M, '-', $D, 'T', $hh, ':', $m, ':', $s)"/>

    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>

XML

<?xml version="1.0" encoding="utf-8" standalone="no"?>
<item>
  <date value="4/1/2013 5:13:41 PM"/>
</item>

output

<?xml version="1.0" encoding="utf-8"?>
<item>
   <date>2013-4-1T05:13:41</date>
</item>