Date fields in Marketing Cloud can be challenging. They are stored in a specific format that includes a time component, and you may have to deal with different time zones.
DESelect now supports modifying date fields, creating timestamps, and calculating date differences, so any date-related requirement you might have for the output of your selection will be resolved.
Transform a date
Available in: DESelect Plus, DESelect Advanced
With the Transform a Date option, you can add or subtract minutes/hours/days/workdays/weeks/month/quarters/years to a given date. You can also convert the date between different timezones. Lastly, you can also convert the date/time field to a date only.
Let’s say we have a data extension with Orders. All orders from stores worldwide are stored in the same format in this data extension, all in Central Standard Time.
We’re sending out an email to customers in Germany, and want to refer to the date of the customer’s last order, expressed in the local timezone.
We’ll create a new Custom Value on the Target Definition Screen, and call it: Date Last Order. As the type of Custom Value, we choose: Apply Formula to a Field.
Next, we choose: Transform Date.
The field we want to modify is the Date field in the Orders data extension.
We can leave Add’ as is, though one could use this to add minutes/hours/days/workdays/weeks/month/quarters/years to a given date.
For format, we choose Date.
Under Convert Timezone, we enable the switch, and indicate we want to convert our Date field to Central European Standard Time
Our new custom value can now be used in the target data extension just by mapping it like you would do with any other field.
For the purposes of this demonstration, we’ve added 2 fields to the target data extension, Original Order Date and CEST Order Date, so we can clearly see the difference in results.
Once we run the preview of this selection, we see that the date in the CEST Order Date field is 9 hours later than the Original Order Date.
Date difference
Available in: DESelect Plus, DESelect Advanced
You can now easily calculate the difference between 2 dates, be it a field in a data extension or a timestamp. The difference between the 2 dates can be expressed in minutes, hours, days, weeks, months, quarters or years.
Let’s come back to our previous example where we want to email customers regarding their last order. In our email, we want to refer to the number of days since the last purchase.
We’ll create a new custom value from the Target Definition screen and call it Days Since Order. As the type of custom value, we choose Apply formula to a field. On the next screen, choose Date difference’
For Date 1, we’re selecting the Orders data extension and the Date field in that data extension.
For Date 2, we’re going to choose Timestamp and Today.
Finally, we indicate we want to express the difference between the 2 dates in Days.
We can now map this field to the target data extension, and explore the results in the Preview tab. In the Days Since Order column, we can now find the difference between the order date and today, expressed in days.
Timestamp
Available in: DESelect Plus, DESelect Advanced
Using a timestamp you can add the date/date-time the selection runs to your target data extension. This timestamp can also easily be converted into any timezone.
An example of where you’d want to use this: you’re adding a list of contacts to an existing list and want to add the date each record was added.
To add a timestamp, add a new custom value, and choose Apply formula to a field and then Timestamp.
For Format, you can choose Now, which adds today including the current time, or Today, which only adds the date.
If you want to, enable the Convert timezone switch and choose the timezone you want to convert the timestamp into.
In this example, we’re mapping this custom value with a Contact Added column in the target data extension. Once we preview this selection, the date of execution of the query is added to the Contact Added field.
Conclusion
If you’re struggling with using date fields in SFMC, make sure to check out the latest release of DESelect, which provides very easy to use but flexible solutions for about any type of use case you might have. Request a demo here.
