TRANSFORMER
String date conversion
Use a String date conversion to convert a selected field that is formatted as a string into a date and make adjustments to it using the date calculator.
Creating a String date conversion process
To create a String date conversion process, right click in the Transformer design panel, choose Process -> String date conversion. Connect it to the previous process as described in Processes.
Properties
General
-
Name - the name "String date conversion" is generated automatically but it may be changed
-
Description - an optional text describing the process
Audit
-
Modified by - the name of the last user to make changes to the object.
-
Last modified - the date and time when the object was last changed.
-
Created by - the name of the user who created the object.
-
Created - the date and time when the object was created.
Custom
-
Source field - the field containing the value that is to be used in the adjustment. Only fields defined as strings will be in the drop-down list.
-
Target field - from the drop-down list choose the field that should contain the date, or enter the name of a new field
-
Date format - this is the format the string (date) has in the source. Choose one of the formats from the list (see below), or you may enter a format directly in the field. Remember that Date format is case sensitive.
-
dd.MM.yyyy
-
MM.dd.yyyy
-
dd.MM.yy
-
dd/MM/yy
-
MM.dd.yy
-
MM/dd/yy
-
yyyy-MM-dd (corresponds to the ISO format ${this.bop.iso}, available in Token properties)
-
The CorpoTime format is MMM dd, yyyy.
It may be necessary to perform calculations on the string to give it a valid format. If you have more than one columns containing the date element, e.g. day, month, and year, you can combine them in a Calculation process, like this:
${day} + "." + ${month} + "." + ${year}
-
Adjustment function - used to modify the resulting date. Enter an Adjustment function in the field, or click the icon to the right to open the Date calculator. e.g. use +EM to always show the last date in the month containing the date in question.
Example
We want to load data from a source into a BMP data record target. The dates in the source are stored as text, not in date format. They look e.g. like this: 26.04.2015.
There are no dates available in the Date column in BMP data record target configuration because only columns that are stored in date format will appear there. Therefore, we need to convert the dates in the source from text (string) to date before they can be loaded. This can be done by adding a String to date conversion between the source and the target.
The Source field is the column where the dates are stored as text, e.g. "datestring". The Target field is an existing or a new column where the converted values will be stored, e.g. "string converted to date". The Date format is the format the date strings are stored in, in this case: "dd.MM.yyyy". (If they had been stored as 04/26/2015, we would have used MM/dd/yyyy.)
In this example we leave Adjustment function empty, since we just want to load the dates as they are in the source.
Now when we preview the String to date conversion object we see that we now have a new column "string converted to date" that contains the dates in date format. And when we open the BMP data record target configuration the new column is now available in Date column.
