BizTalk 2010 Recipes : Document Mapping - Using the Date and Time Functoids

1. Problem

You are mapping a message in BizTalk Server and need to manipulate date and time fields. Specifically, you must use the current date in order to determine a required date and time in the future, which is added to the outbound message during mapping. Additionally, you must apply the processing time for each document during mapping.

2. Solution

Within a map, use the Date and Time functoids provided with BizTalk Server. To apply the current processing time to the outbound document within the map, take the following steps:

To determine a future date based on the current date, take the following steps:

3. How It Works

An example of a map that uses the Date and Time functoids is shown in Figure 1.

Figure 1. Using the Date and Time Functoids

This example captures the current date and time in the TransactionProcessingDateTime element, which is supplied by the Date and Time functoid. The format of the date and time produced by the Date and Time functoid is YYYY-MM-DDThh:mm:ss, which is ISO 8601-compliant. The time values are notated in 24-hour format.

The future date that the deposited funds will be available is captured in the FundsAvailableDateTime element. This value is based off the current date, supplied by the Date functoid, with a single day added to it to provide tomorrow's date, which is supplied by the Add Days functoid. The format of the Date functoid's output is YYYY-MM-DD, while the Add Days functoid accepts dates formatted as YYYY-MM-DD or YYYY-MM-DDThh:mm:ss. The format of the Add Days functoid's output is YYYY-MM-DD. All date formats used by these two functoids are ISO 8601–compliant. The time values are notated in 24-hour format.

The XML in Listing 1 represents one possible instance of the source schema.

Example 1. Sample Source Instance for the Date/Time Functoid Example

<ns0:ATMDeposit xmlns:ns0="http:// DateTimeFunctoids.ATMDeposit">
  <ATMNumber>00111</ATMNumber>
  <AccountNumber>123456</AccountNumber>
  <Amount>100.00</Amount>
  <Currency>USD</Currency>
</ns0:ATMDeposit>

When passed to the map in Figure 1, this XML message will produce the outbound XML document shown in Listing 2.

Example 1. Sample Output Instance for the Date/Time Functoid Example

<ns0:BankDeposit xmlns:ns0="http://Mapping.BankDeposit">
  <TransactionSourceNumber>00111</TransactionSourceNumber>
  <Account>123456</Account>
  <Amount>100.00</Amount>
  <Currency>USD</Currency>
  <TransactionProcessingDateTime>2010-09-06T16:32:05</TransactionProcessingDateTime>
  <FundsAvailableDateTime>2010-09-07</FundsAvailableDateTime>
</ns0:BankDeposit>

					  

The Date and Time functoids provide a baseline set of functionality when needing to interrogate and manipulate date and time values. In the bank account example, the Date and Time functoid provides the current date and time, allowing a timestamp to be applied to the outbound document, indicating the time that the message was processed. Often, messages include an element or elements detailing the time that the message pertains to, or the time at which a message was created. The Date and Time functoid is useful when the actual processing time (in this example, the time a deposit is processed within a bank's integration system) is required, which will likely be different from any time value embedded in the source document. The difference between date and time values within a source instance message and the actual processing time of the message can be particularly important when dealing with batched or time-delayed processes.

This example also shows how you can use the Date and Add Days functoids in conjunction to calculate a future date based on the day a message is processed. The current date is provided by the Date functoid, which is used by the Add Days functoid to come up with a date that is a specified number of days in the future. In the bank account scenario, this functionality is used to determine the day on which deposited funds are available. Business rules like this can be common among financial institutions, as validation procedures are often required when dealing with monetary transactions.

This bank account example can be extended to illustrate how the Time functoid can be used to add a time component to the data capturing the date at which deposited funds are available. Currently, the example simply calculates a date for this value, but it is likely that a financial institution would additionally need to know the time at which deposited funds should be made available for use. Since the Add Days functoid produces only date values (without a time component), you must use a Time functoid, in addition to a String Concatenate functoid. To implement this enhancement, take the following steps:

Figure 3 shows an example of a map implementing these changes.

Figure 3. Using the Time functoid

Based on the same source XML used in the preceding example, the map in Figure 3 will produce the XML document shown in Listing 3, with a time component included in the FundsAvailableDateTime element.

Example 3. Sample Output Instance for the Time Functoid Example

<ns0:BankDeposit xmlns:ns0="http://Mapping.BankDeposit">
  <TransactionSourceNumber>00111</TransactionSourceNumber>
  <Account>123456</Account>
  <Amount>100.00</Amount>
  <Currency>USD</Currency>
  <TransactionProcessingDateTime>2010-09-06T16:32:04</TransactionProcessingDateTime>
  <FundsAvailableDateTime>2010-09-07T16:32:05</FundsAvailableDateTime>
</ns0:BankDeposit>

					  

One common challenge when dealing with datetime values is standardizing on a common format. This issue can be seen with dates adhering to the MM-DD-YYYY (common in the United States) or DD-MM-YYYY (common in Europe and other areas) format. Unfortunately, BizTalk Server does not have any Date and Time functoids that do complex datetime formatting. There are a number of ways to handle this issue. The simplest way is to use string functoids provided with BizTalk Server to manipulate and standardize the order of year, month, and day values. Specifically, you can use String Extract functoids to pull out values within a date (in this scenario, you would pull out the DD, MM, and YYYY values in three separate String Extract functoids), and then a String Concatenate functoid to combine the individual values in the correct format (in this scenario, you would append the three values in an order of YYYY-MM-DD).