Date formats and time zone can be a tricky thing to manage in Marketing Cloud due to how each studio handles and displays date time information; I have spent more than my fair share of time testing formats and time zones to ensure the correct date is captured. So here are my tips and technical documentation of how to navigate this part of the platform.
What time zone is Marketing Cloud in?
The Salesforce Marketing Cloud servers operate in Central Standard Time (CST), also known as GMT-6. This means it’s 6 hours behind GMT+0, UTC and “Zulu” time. If it’s midday in Greenwich (UK), then it’s only 6am in CST; 6 hours behind. All data and programming languages will operate with CST as the server time, so you have to account for this in your code!
What time zone should I store Marketing Cloud data in?
Not really a clean cut answer here – store you customer/business data as you need to use it. However I recommend storing data in the server time (CST, GMT-6) so that it’s accurate at the data level. If you know the underlaying data is correct, then you can choose how to display it with some simple formatting tricks. Attempting to store date data in your local time zone may look good on the surface, but you’re going to run into issues later when you try and use SSJS or SQL to compare dates.
What date formats does Marketing Cloud accept?
Pretty much all of them… Marketing Cloud is built to handle all the standard localisations of date formats. The date format setting on file imports gives you some more direct control over this. However if you are looking for the “Best” formats to use when importing data into Marketing Cloud – here is my recommendations:
- YYYY-MM-DD hh:mm:ssZ (eg: 2021-07-14 12:05:13Z, where Z indicates GMT+0, aka Zulu time)
- YYYY-MM-DDThh:mm:ssZ (eg: 2021-07-14T12:05:13Z, where Z indicates GMT+0, aka Zulu time)
- YYYY-MM-DD hh:mm:ss+##:00 (eg: 2021-07-14 12:05:13+10:00, where +10 indicates GMT+10)
- YYYY-MM-DDThh:mm:ss+##:00 (eg: 2021-07-14T12:05:13+10:00, where +10 indicates GMT+10)
- YYYY-MM-DD hh:mm:ss.SSSZ (eg: 2021-07-14 12:05:13.000Z, where Z indicates GMT+0, aka Zulu time)
- YYYY-MM-DDThh:mm:ss.SSSZ (eg: 2021-07-14T12:05:13.000Z, where Z indicates GMT+0, aka Zulu time)
- YYYY-MM-DD hh:mm:ss.SSS+##:00 (eg: 2021-07-14 12:05:13.000+10:00, where +10 indicates GMT+10)
- YYYY-MM-DDThh:mm:ss.SSS+#:00 (eg: 2021-07-14T12:05:13.000+10:00, where +10 indicates GMT+10)
The above formats are all subsets of the ISO 8601 international date format, and are the most command (and least ambiguous) ways to represent datetimes. Try to use one of these formats when bringing data into Marketing Cloud to ensure it’s being handled correctly on import.
How can I convert DateTime data in Marketing Cloud?
There are a few ways you can display datetime values in Marketing Cloud.
If you are looking for a way to print your datetime data on a CloudPage or Email, then you can use AMPscript functions like Format() and FormatDate(), or SSJS Functions like Format() or Get Date Parts. You can also use string alteration functions to manually recreate any date format you need.
For SQL date in Marketing Cloud you can use functions like Convert(), Cast() and DatePart(). You can also assemble your own custom date format using Concatenation and string alteration functions if needed (although not recommended).
How can I convert time zones in Marketing Cloud?
Offt, this one is not as easy.
The only time zone converter you have out of the box is the “Server to Local Time Zone” functions in AMPscript, SSJS and SQL. This can be really useful for taking your data stored in Server Time GMT-6 and parsing it into Local Time, however there are a few known quirks with these functions. One such quirk is that it will convert the actual date time (the date & hours) however it wont update the GMT to the correct value, which can cause downstream issues for anyone receiving your data.
You can play around with the FormatDate() (and similar) functions to produce a usable time zone converted date, however you’re likely going to need some string alteration to clean it up a bit. My preferred way is to manually add/remove hours from the date object. Given the server is in CST (GMT-6), you can easily add 6 hours to that time (AMPscript, SSJS & SQL) to enforce Zulu time. From here you can safely export the “YYYY-MM-DD hh:mm:ssZ” format or use a data formatter to convert the Zulu timestamp to a localised one.
Comments are closed.