Reading Time: 8 minutes

When integration involves different applications, systems, or databases, we face a common challenge: how do we bridge between data formats and how can we provide interoperability for fields that store dates and date/time values?  

I recently encountered a scenario in which I had to integrate a SaaS application to a legacy application that published data on an IBM Websphere MQ in fixed position format. For this integration process, I needed to parse each line, then transform the data using DataWeave to the request payload structure for a SOAP-based web service endpoint exposed by the SaaS application and, finally, invoke this endpoint using the Web Service Consumer connector.

latest report
Learn why we are the Leaders in API management and iPaaS
Read reports

Most of the mappings were straightforward and I used different DataWeave operators and constructs to transform the data. This included when/or/otherwise, checking for nulls, as well as implementing dynamic lookups to enrich data within DataWeave. Please refer to my other blog posts, where I cover these topics in detail.  

In the process, I also needed to take date/time stamps that were presented as simple strings in the legacy application and convert them to the standard XML DateTime type, which is the format that is understood by the SaaS application. Performing this conversion was fairly straightforward, but still needed some trial and error to get the syntax correct. In this HowTo guide, I will show users how they can perform this data arithmetic conversion using DataWeave.

Here are the steps I took for the project:

  1. In legacy systems, the stored date appears in the following non-standard format: ddMMMyyyyHHmm, for example, it would appear as: 29JUN20171530 to represent 3:30 pm on June 29, 2017
  2. The date above is written differently than the standard XML DateTime, as it doesn’t include information on the seconds/milliseconds as well as the timezone.
  3. After the above observation, I confirmed with the legacy application SME that it was acceptable for me to assume that the input timestamp is 0 seconds and 0 milliseconds, and the timezone is UTC.
  4. I then researched the syntax for how to create a date mask for DataWeave in order to parse the input date, specify the desired output format (which was the XML DateTime type), and append the timezone information at the end of the string, as per the W3C specification for XML DateTime.
  5. The final DataWeave expression was as follows:
 

SpecifiedDateTime: (payload.datetimestamp as :localdatetime {format: “ddMMMyyyyHHmm”} as :string {format: “yyyy-MM-dd’T’HH:mm:ss.SS”}) ++ |-00:00|

  1. The syntax above takes the date, which is stored in “payload.datetimestamp,” then coerces it to “localdatetime,” which is DataWeave’s format for storing date and time when the timezone is not specified.
  2. As part of this coercion, we provided the data mask in order to specify how to interpret the contents of the string “ddMMMyyyyHHmm,” in which the letters in the mask denote the element of the date it represents at those string positions. For example “yyyy” from position 6-9 in the string translates to the full year (i.e. “2017”). Please note that the above mask is case sensitive, so you will need to verify the conventions in the documentation references provided below.  
  3. Next, we specified the output format: “yyyy-MM-dd’T’HH:mm:ss.SS” to take our data, which is now a date that DataWeave understands and can convert to the XML DateTime type format. Again, please note that you have to add “ss.SS” in order to provide the seconds/milliseconds to the end of the date, as both the seconds and milliseconds are set to 0 as part of the conversion to “localdatetime” in DataWeave.  
  4. Finally, the last piece of information we added is the timezone, and we did this by concatenating: |-00:00| to our date, which specifies that it is in fact in the UTC time zone.
  5. If I need to add/subtract durations to my date, then DataWeave allows you to do this very easily.  For example, if I needed to add a year to a date, I would use the following syntax:

SpecifiedDateTime: |2003-10-01T23:57:59Z| + |P1Y|

Where “+ |P1Y|” adds a period of 1 year to the date. In order to subtract a year, we would replace the “+” with a minus “-”.  In addition to modifying year, DataWeave allows you to perform date arithmetic for months, days, minutes and seconds.  Please refer to the documentation references below for more details.

With this feature, users can turn to DataWeave in order to convert strings to date/time stamps and, in turn, perform data arithmetic. This feature can enable Mule developers to easily mediate and interoperate with disparate applications and systems.  

For more information, please refer to the documents below: