During 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 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.

Understanding Date time Format

Datetime data is a complex primitive data type which contains multiple integer 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 3rd day of a month, the value it will produce is 3.

dd – Represents mandatory 2 digit value of day of a particular date. If you specify dd  for 3rd day of a month, it will be represented as 03.

ddd – Represents the abbreviated name of the day of week. Result would be Mon, Tue etc.

dddd – Represents the full name of the date in a week. Result would be Monday, Tuesday etc.

m – Represents minutes of a particular datetime. 0 through 59 is valid values.

mm – Two digit representation of minute in a datetime. 00 through 59 are valid values.

M – Represents month of a particular date. 1 – 12 is valid here.

MM – Represents two digit representation of month. 01 – 12 is valid values.

MMM – Abbreviation version of month. Eg, Jan, Feb, Mar etc.

MMMM – Represents 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 digit. 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 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 target xml schema by getting date value of CreatedDate on Source schema and formatting it into a specific format. You can use any of the format specified in the string to indicate a particular formatted output.

Please note there are 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

Here in 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.

Also Check out: Working with DateTime