25. Tranform date fields

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: You have a data extension (Orders) that contains a column with order dates and you want to add a new field to your target data extension that contains these order dates but in a client's local time zone (e.g. CEST).

Note: This scenario uses feature Custom Values, which is available in DESelect Plus and Advanced.

1. Selection Criteria

On the selections overview screen, click on New to create a new selection. 

Drag data extension DESELECT_DEMO_Orders to the Selected Data Extensions section. 

2. Target Data Extension

Hit the Create Data Extension button, enter a name, e.g. Orders Target, and press Save.

Select the following fields by either double-clicking on them or dragging them to the section on the right:

• ContactId and Date from DESELECT_DEMO_Orders.
• Drag Date again, and rename it to CEST Order Date.

Click Save Data Extension.

Click on the Delete icon next to the Date field that's mapped to CEST Order Date.

We have now created an extra field in our target data extension that we want to populate with our order dates converted to CEST time zone. This field is not mapped to any fields in the source data extensions.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: CEST Order Date and hit Next.
• Choose Type: Apply formula to a field.
• Choose Type: Transform Date.
• On the first drop-down list with label Data Extension choose DESELECT_DEMO_Orders.
• On the second drop-down list with label Field choose Date.
• Here we are not interested in changing the time, therefore we will leave the fields next to Add label as they are. Otherwise, the input field would be used to set the time interval and the drop-down list to declare the date part.
• Under the Format label choose Date to not include order time.
• Click on the Convert Timezone toggle button to enable conversion.
• Select (UTC + 02:00) Central European Standard Time (CEST) from Convert to timezone drop-down list.
• Hit Save.

The logic defined above will, for each row, convert the value of Date column from Central Standard Time (CST) time zone to Central European Standard Time (CEST) time zone by adding 9 hours.

Now that the custom value CEST Order Date has been created, we can map it to the field CEST Order Date by drag-and-dropping it from Custom Values to the CEST Order Date mapping field.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown with fields

• ContactId and Date from DESELECT_DEMO_Orders
• CEST Order Date, populated with dates in Central European Standard Time (CEST) time zone.

In the shown results, the date in the CEST Order Date field is 9 hours later than in the Date field.