4

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.

Improve this question
3
  • There are number of question with answer available for date format. Have you seen and tried them? Commented Jun 13, 2013 at 7:29
  • 1
    It depends a little whether 4/1/2013 is 4th January or 1st April. But essentially there's no easy answer, you just have to parse out the components of the date using string manipulation (regexes in XSLT 2.0) and then reassemble the string in the right order. Tedious, but not difficult. Commented Jun 13, 2013 at 9:37
  • 2
    @MichaelKay: It is clear from the ISO date time string that the date is 1 April. Commented Jun 13, 2013 at 12:01

6 Answers 6

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I would change <xsl:variable name="dateTime"><xsl:value-of select="..."/></xsl:variable> to <xsl:variable name="dateTime" select="..."/>.
Great answer except for 1 point. You missed the 12AM -> 00HH conversion. You can replace "number(regex-group(4))" with "number(regex-group(4)) mod 12" in the otherwise part for hours
4

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>
Improve this answer

1 Comment

While I am here: this result is incorrect: 5:13:41 PM needs to be transformed into 17:13:41. Otherwise the output will be 12 hours earlier that the input.
3

Have a look at ESXLT. Many XSL implementations already support the functions out of the box, and they provide fallbacks in case yours does not support it.

If you have fully-compliant implementation, you can do this:

<?xml ...
  xmlns.date="http://exslt.org/dates-and-times"
  ...

<xsl:variable name="xsdate" select="date:parse-date(Date/@value, 'M/d/yyyy h:mm:ss a')" />

<xsl:value-of select="$xsdate" />

I cheated and used the result of date:parse directly. One often uses the date:format function to format the date into a particular format.

I've honestly been struggling with getting the "fallback" functions from EXSLT working for me in Apache Xalan. (Using non-core EXSLT date functions with Xalan Java)

IMO it would be better to have the date in xs:dateTime format in the first place, and convert to some localized format afterward. You can use the date:format function to format the date as necessary, and the date:format function enjoys much wider support amongst XSLT processors.

Improve this answer

5 Comments

Why don't you post an actual answer, i.e some code that would return the required output from the given input? As it happens, I don't know of any EXSLT function that would do that, but then I don't know everything.
Congratulations, I stand corrected. But which processor supports this function "out of the box"? I don't know of any.
@michael.hor257k Yeah, I don't think there are any, honestly. I haven't checked to see whether the "fallback" options provided by ESXLT will work for parse-date. If using Xalan-Java, you can use the technique I used in stackoverflow.com/questions/22081400/… and parse-and-reformat the date yourself. Still better-off using xs:dateTime dates in your XML in the first place.
The <xsl:fallback> element is designed to handle elements, not functions. For functions, use the function-available() function.
@michael.hor257k I'm being sloppy with my nomenclature: I meant ESXLT's "other options" for using their functions when the XSLT implementation does not provide them... not that <xsl:fallback> should be used.
1

Using XSLT 2.0 you can construct an xs:dateTime value by extracting the components from Date/@value using string functions, then you can format if needed using format-dateTime: http://www.w3.org/TR/xslt20/#format-date. Extracting the date components like year, month, day should be straightforward, obviously for the time you need to some more work to pay attention to the PM or AM 12-hour clock.

Improve this answer

1 Comment

we could use an actual example here :)
0

@adhockgeek's answer worked almost perfectly for me. I only had to add a condition so that it not add 12 to the hour if the value was currently 12. IE, 12:15 PM != 24:15 AM.

I created a template from his code that can be called repeatedly within a transform:

<xsl:template name="FormatDate">
    <xsl:param name="dt"/>
    <xsl:analyze-string select="$dt" 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' and regex-group(4) != '12'">
                        <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')))" />
            <xsl:value-of select="$dateTime"/>
        </xsl:matching-substring>
    </xsl:analyze-string>
</xsl:template>
Improve this answer

Comments

0

Code with substring-before and substring-after works, but more checks should be added: 12AM => 00 and 12PM => 12 something like this:

 <xsl:variable name="hh">
    <xsl:choose>
      <xsl:when test="$ampm = 'AM' and $h='12'">00</xsl:when>
      <xsl:when test="$ampm = 'PM' and $h!='12'">
        <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>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.