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 timezone (e.g. CEST).

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

1. Selection Criteria

On the overview of Selections, click on New Selection to create a new one. 

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:

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

Click Save Data Extension > Create.

Click on the delete icon next to the Order 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 the CEST timezone.

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.

A modal will appear where you can do the setting.

• Under Data Extension, choose DESELECT_DEMO_Orders.
• Under Field, choose Order 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, choose Date to not include order time.
• Click on the Convert timezone toggle button to enable conversion.
• Under Convert from timezone, by default is the Central Standard Time (CST) (as this is what SFMC uses). If you have priorly converted it to a different timezone, you can choose that timezone (in which timezone is your data formatted).
• Under the Convert to timezone, choose the timezone to which you want to convert your timestamp to, in this case, choose the Central European Standard Time (CEST).
• Click Save.

The logic defined above will, for each row, convert the value of the Order Date column from the Central Standard Time (CST) timezone to the Central European Standard Time (CEST) timezone.

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

• CustomerId and Order Date from DESELECT_DEMO_Orders
• CEST Order Date, populated with dates in the Central European Standard Time (CEST) timezone.

In the shown results, the date in the CEST Order Date field is 9 hours later than in the Order Date field (as the difference of the timezones).