The supplier in this scenario also keeps the starting date for a customer’s account (in the CRE_DATE column). On the Mapping tab, however, the target column CRE_DATE has no corresponding source column and was not automatically mapped. This is so because the starting date was originally entered in a character-based CREATE_DATE column. The task now is to transfer the CHARACTER source column date to a target column whose datatype is DATE.
In this case, you can use a Connect CDC built-in function, dateformat, to operate on the character-formatted source column value and output a suitably formatted result for the target column:
-
In the Method column for the CRE_DATE target column, select Expression.
-
Click the Edit source button at the bottom of the window to display the Target Column Expression window.
- From the Method list, select Date format3, then click the Insert button.
The dateformat function name is displayed in the expression workbox to the left.
The Date format3 method information (description, return value, and parameters) is displayed below the Method list.
You can use the displayed function information as a guide for specifying the function expression in the workbox to the left. For more detailed function information, click the Help button and find the link to the predefined methods summary.
Note: Date format3 and dateformat are described in detail in System Reference. -
After placing your cursor between the parentheses following dateformat in the expression workbox, provide a value for the first dateformat parameter (input), the character column you are converting. From the Column list, select CREATE_DATE and click the Insert button.
-
Select the comma operator (,) from the Operator list, then click the Insert button.
-
Provide a value for the second dateformat parameter (output_format), the pattern of the date output sent to the target database (Oracle, in our example). The valid dateformat pattern characters are described in the detailed information for the function in the online Help.
-
Type the following string in the expression workbox to define the standard Oracle date format:
‘yyyy-MM-dd hh:mm:ss.SSSSSS’ -
After adding a comma to the previous string, provide the following value for the the third dateformat parameter (input_format), the format of the date values in the CREATE_DATE source column. For the month/day/two-number-year values in our example, specify:
‘MM/dd/yy’ - Click the Validate button to verify that the expression is syntactically valid.
-
Click OK once the expression is valid.
-
Click OK to close the Target Column Expression dialog.
The Source Data value for the CRE_DATE column displays the expression.
-
Click OK from the Mapping tab to close the Sending Table Properties dialog box.
The Connect CDC Director does additional verification that includes, for example, the compatibility of the datatypes and of the built-in method you have selected. If your entries are valid, the dialog box closes without any other message.
If necessary, you may find useful debugging information in the Connect CDC Director log, omnidir.log, and in the Expression Handler log, omnijvm.log.