DESelect August ’20 Release: Modifying date fields

DESelect August ’20 Release: Modifying date fields to make segmentation in Salesforce Marketing Cloud easier

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.

  • What’s new in DESelect: August 2022

    The DESelect team has been looking forward to this release with many eagerly requested features finally here and ready for you! This August we’re introducing several features and UIUX enhancements designed to keep your workflow smooth, simplify data management, and empower Admin users.   Building, revising and managing Waterfall Selections is now easier, and faster, […]
  • What’s new in DESelect: June 2022

    Summer is finally here along with powerful new features from our team. Happy June! It’s Garrett again with big product updates for you as we start the summer. DESelect proudly opened the door to non-technical marketers by ending the technical dependencies around SQL. We also take pride in equipping technical marketers with tools that increase […]
  • What’s new in DESelect: April 2022

    Get more time back in your day with DESelect. That’s not an April Fools’ joke! Welcome to April! It’s Garrett, from DESelect, here to share this month’s Product updates. It’s no secret that our no-code products already help thousands of marketers unlock the full power of SQL for more effective marketing campaigns. Today we’re excited […]
  • DESelect February ’22 Release: Scheduling of waterfall selections and moving around selected data extensions

    It is the first release of the year so let’s make it interesting! As you may already know, DESelect loves to bring customer’s ideas to life. And so, we are introducing 2 new powerful functionalities that come by popular demand: scheduling of waterfall selections and moving around selected data extensions.   Let’s get started! Scheduling […]

Stay Connected

Join our newsletter to receive updates and helpful SFMC guides

Subscribe to the Newsletter
Receive Salesforce Marketing Cloud tips,
tricks, and DESelect updates

By clicking the “Subscribe now” button, you agree to the DESelect Terms of Use and Privacy Policy.