During the transformation of data between source and destination, the formatting of the data sometimes becomes crucial. The data transformation logic requires data to be perfectly compatible with the destination application such that things are correctly transformed and pushed to the target application. For instance, if your source has a date format of say ‘yyyy/MM/dd’ while your target application requires it to be different, you need your transformation to be compatible with the target application.
As an implementer, you need to ensure the data is correctly transformed to the data which your target application understands. To do such a scenario, you need to do mapping of the source field to be wrapped around a function such that it transforms the data to a correct format.
Connect all your business applications under one single platform to automate the business process and increase your productivity and efficiency!
Understanding Date time Format
Datetime data is a complex primitive data type that contains multiple integers to indicate different values. Let us understand the format specifier options for a DateTime field.
- d – Represents the day of a particular date. For instance, if you use d to indicate the 3rd day of a month, the value it will produce is 3.
- dd – Represents mandatory 2 digit value of the day of a particular date. If you specify dd for the 3rd day of a month, it will be represented as 03.
- ddd – Represents the abbreviated name of the day of the week. The result would be Mon, Tue, etc.
- dddd – Represents the full name of the date in a week. Results would be Monday, Tuesday, etc.
- m – Represents minutes of a particular DateTime. 0 through 59 are valid values.
- mm – Two digit representation of minute in a DateTime. 00 through 59 are valid values.
- M – Represents the month of a particular date. 1 – 12 is valid here.
- MM – Represents two-digit representation of month. 01 – 12 are valid values.
- MMM – Abbreviation version of month. Eg, Jan, Feb, Mar, etc.
- MMMM – Represents the full name of the month. Eg. January, February, March, etc.
- y – The year, 0 to 99.
- yy – The year, 00 to 99.
- yyy – This is the year in 3 digits. 001, 900, 1900 etc.
- yyyy – This is the year in four-digit. 0001, 0900, 1900 etc.
- t – First character of AM/PM designator. For instance, P is for PM.
- tt – The AM/PM designator.
- h, hh – Hour representation in 12-hour clock.
- H, HH – Represents the 24-hour representation of an hour in a clock.
- s – Represents the second.
- ss – Represents the two-digit representation of second.
- K – Specifies the timezone information.
These parameters could be directly used inside a mapping section of a transformation. You can use the generic function FormatDate to reformat the values passed to the transformer.
The image shows how you can map the Timestamp attribute of the target XML schema by getting the date value of CreatedDate on Source schema and formatting it into a specific format. You can use any of the formats specified in the string to indicate a particular formatted output.
Please note there is two formats string specifier present in the format section. The 2nd format string specifier will indicate the source DateTime format, while the 3rd parameter will indicate the resultant DateTime format string.
Some common formats
Herein all the days, I will use the format string for a date October 1st, 2019 at 04:10.29.299pm
- MMddyyyy – 10012019
- MM/dd/yyyy – 10/01/2019
- MM-dd-yyyy – 10-01-2019
- yyyyMMdd HHmmss – 20191001 161029
- yyyy-MM-dd’T’HH:mm:ss.SSSZZ – 2019-10-01T16:10:29.299 – 05:00
Similar to the above examples, you can create any date format string based on the requirements you need.
APPSeCONNECT is a smart and robust business application integration platform that seamlessly connects all your business applications with each other to streamline operations and facilitate the free flow of data across the platforms. By moving into the region of iPaaS, APPSeCONNECT proves to be a best-in-the-class platform that easily connects systems and automates the business process. Do you have multiple systems running? Connect all your business applications under one single platform to automate the business process and increase your productivity and efficiency!
