Dates and Times in Salesforce Marketing Cloud

Photo by noor Younis on Unsplash

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.

• ExactTarget/Marketing Cloud system time is hard coded to UTC-6 (UTC minus six hours) and the system time does not change with standard versus daylight savings time.

(Unless your account is on Stack 4, which is in US Mountain time — UTC minus 7 hours).

• In SQL, it’s equivalent to AT TIME ZONE ‘Central America Standard Time' UTC-06. This is NOT to be confused with Central Standard Time which often does observe daylight savings time during summer and changes to Central Daylight Time (CDT) which is UTC-05.
• https://bornsql.ca/blog/dates-and-times-in-sql-server-at-time-zone/
• When utilising the “use current date” function in a Data Extension date field, this is set to the system date UTC-06.

• LocalDateToSystemDate() will convert the local date to the system date. The local date/time is defined in the user’s local time in settings.
• The NOW() function returns the system date that can be easily converted to a local date using SystemDateToLocalDate().

Time Zone User Preferences

You can view time-related data in your chosen time zone and culture rather than the system defaults CST and en-US. Set these preferences in your user record under Users in Setup menu.

These settings can be changed at the user or account levels. Time zone and culture code settings at the user level take priority over account settings. The changes reflect throughout Marketing Cloud user interface. Times shown in journey and triggered send tracking information remains in CST regardless of this setting.

Reference documentation here.

Tracking Extract

Selecting the checkbox “Use Local TZin Query” will convert dates in the tracking output to the selected timezone from the dropdown menu within the Data Extract Activity Configuration window.

I fortunately ran a number of tests extracting sent data for an email send. And each time, the date and time was correctly converted to the timezone in the dropdown menu. Do be mindful of possibilities for daylight savings differentials.

Use Local TZin Query

When the TZ checkbox is not checked, the query will pull the data from midnight to midnight System Time.

Known Bug: DE Created/Modified Date

There is a known issue bug where Data Extension Created Date and the Modified Dates are NOT in the local user timezone. In the screenshot below, you’ll notice Contact Builder (left) has different Created/Modified System Dates compared to Email Studio (right).

Another Gotcha: Report Output defaults to CST

Reports in Email Studio and Analytics Builder often require you to specify a Time Zone and Culture Code within the Report Parameters. However, the report will ignore the selected Time Zone and will output dates and times in CST (UTC-06).

Just remember reports always generates the time in CST, the reason being that the Marketing Cloud Data centers are in CST time zone and hence that time is used.

Here’s a ticket to a Known Issues (In Review) on this point: https://trailblazer.salesforce.com/issues_view?id=a1p3A0000008gdsQAA

…