Discover our knowledge. Read our blogs!

Learn more

We build all our solutions with WSO2 and we are proud that we are WSO2 Premier Certified Integration Partner and Value-Added Reseller.

Learn more

Date Formatting Part 1: A Practical Integration Issue

9 min read

date format - calendarA common issue in integration is the issue of the format of your data. This problem arises often in health-care and government. Imagine a system in which every hospital and government works with tenders to attract parties to take care of data flow. Every party uses a different format to get their data across. For instance, one party denotes a birth date as ‘ddmmyyyy’ (European), another party denotes that same birth date as ‘yyyymmdd’ (American). As you will see later on the used format is not mentioned anywhere. There are no dashes or other separators to give clues about the format.

The date field always contains 8 numbers. Now imagine a situation in which you have to create an integration that both aforementioned parties have to deliver upon. Your integration will have to deliver one standard, let’s say the American.  Let’s assume that no other formats than the aforementioned are being delivered to your integration. Here’s the puzzle; how to handle this problem? As often is the case, there are multiple solutions. Let’s consider a mapping, which contains information about which party uses which format. There are two disadvantages:

  • the mapping you make has to be kept up to date.
  • the integration fails if a party switches format without letting you know.

In this blog, we are working with an XSLT transformation: it is simple, effective, and low maintenance.

First let’s consider the constraints of the date formats:

Date formats

Let’s have look at positions 5 and 6 of the date string. In the European format those are the first two digits of the year. For persons that are alive today, the value ‘19’ or ‘20’ may be expected on these positions. In the American date format the 5th and 6th position describe the month, and that may not exceed 12. A rule to distinguish between the formats could then be as simple as: 

(1) If the value of positions 5 and 6 exceeds the number 12, then the format is European. Otherwise it is American.

When looking at positions 5 through 8 of the date string, it can only be the American format if the value does not exceed ‘1231’. Both the 12 and the 31 are meaningful units, and one can only generalize to this rule because the latter always has a larger delimiter. Put in a rule form, it looks like this: 

(2) If the value of positions 5 through 8 exceeds the number 1231, then the format is European. Else it is American. 

Of these two rules, the second is slightly better, because one would include year 1232 to 1299 as acceptable birth years. The mentioned rules are both sufficient, considering the aforementioned constraint that either the European or the American notation is used, and the notion that the birth year must be reasonable for a person that is alive today. 

Integration

Now that we know how to distinguish between the European and American standards, let’s talk integration.

Here is the message from the one party:

<person xmlns="test.yenlo.com">
    <id>1</id>
    <birthdate>20112012</birthdate>
</person>


And here’s the message of the other party:

<person xmlns="test.yenlo.com">
    <id>2</id>
    <birthdate>20111231</birthdate>
</person>


The second message could be passed on without any work. The first however, requires some transformation. XSLT is a proper tool to do transformations like the one required. Assuming you already have a project in Eclipse, right-click the registry folder in the ‘project explorer’ window and select ‘new registry resource’:

dateFormat1-registry

Select ‘from existing template’. Give it a name, select template ‘XSLT File’. Set your registry (gov or conf) and registry path (e.g. ‘xslt’). Click Finish. You can also use the Dashboard functionality of DeveloperStudio. The result is the same, the only difference is the way you do it.

New Registry Resource - date format

New Registry Format 2 - Date format

The following transformation should do the trick:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:ns1="test.yenlo.com"
    exclude-result-prefixes="xs ns1" version="2.0">
    <xsl:output indent="yes"/>
   
    <xsl:template match="/ns1:person">
      <xsl:copy>
        <xsl:copy-of select="ns1:id"/>
        <birthdate xmlns="test.yenlo.com">
          <xsl:variable name="birthdate" select="ns1:birthdate"/>
          <xsl:choose>
            <xsl:when test="xs:int(substring($birthdate, 5, 4))               gt 1231">
              <xsl:variable name="day"
               select="substring($birthdate,1,2)"/>

              <xsl:variable name="month"
               select="substring($birthdate, 3, 2)"/>

              <xsl:variable name="year"
               select="substring($birthdate, 5, 4)"/>

              <xsl:value-of select="concat($year,$month,$day)"/>
            </xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="$birthdate"/>
            </xsl:otherwise>
          </xsl:choose>
        </birthdate>
      </xsl:copy>
    </xsl:template>
</xsl:stylesheet>

 
Now all that is left for your integration is to call the XSLT script from your proxy. You can do that like this:

<xslt key="gov:/xslt/dateformatter.xslt"/>


Try it yourself

These are the high-level steps.

  • Start WSO2 ESB Server (or Enterprise Integrator)
  • Create a project in Developer Studio (Eclipse)
  • Create proxy
  • Create registry resource
  • Implement the transformation

Downloading the examples and importing them in Developer Studio will make it easy to recreate this example. With the date format examples that we provide on bitbucket you can try it out yourself! Don't hesitate to ask your questions in the comment section below.

 

Care to share?
   
Picture of Siger Steenstra
Published February 28, 2019

Siger Steenstra

Siger is a certified WSO2 ESB / EI consultant with experience in several projects. His knowledge of algorithms and various programming patterns are of great use while solving integration challenges for our customers.

Responses

Stay up to date with the latest articles