Unexpected behavior of DateParse in AMPscript
Different results in an email preview and the actual send sounds like a nightmare to you? Results dependent on the current user sound even worse? If it does to you, and you plan on using the DateParse function in AMPscript, you definitely need to be aware of this behavior!
My use case
I needed to calculate start and end dates of the previous month to fetch data from within this time period from Sales Cloud. To save myself some time I used an adapted version of Adam Spriggs’ code snippet for calculating the first and last days of the month.
Knowing that the DateParse function is dependent on the Business Unit’s time format settings, I changed the call accordingly to reflect that.
As a short reminder, here’s what ampscript.guide writes about the date format dependency of the DateParse function:
NOTE: The function uses the date format defined in the respective Business Unit. For example, MM/DD/YYYY when the Business Unit date format is set to English (United States) or DD/MM/YYYY when the Business Unit date format is set to English (United Kingdom). If you attempt to parse a string in an incorrect format based on the Business Unit date format, for example 30/12/2020 when the Business Unit date format is set to English (United States), then the function will return a String was not recognized as a valid DateTime error.
Source: ampscript.guide/dateparse
So far so good, and everything worked fine in the preview.
However, once I sent the email - luckily to a test audience - I found out that the code didn’t work as expected based on the outcome in the preview, even when using the same data extension and subscribers for the preview 😞
Why did this happen?
After some debugging and playing around with the code, I figured out that depending on the message context the DateParse-function behaves differently:
- For the preview, the date format of the Marketing Cloud Business Unit is used
- For sends and test sends, the date format based on the user’s Culture Code is used
Here are the workarounds I came up with
To solve this issue, I came up with two solutions: one which is pretty static, but works in all scenarios of my use case. And another one that dynamically fetches the start and end dates of a month, but isn’t as robust as you need to know the Culture Code of the user working with the email.
The more dynamic approach
If you know what Culture Code the user that initiates the send uses, you can create a code checking for the send context like this:
%%[
SET @today = Now()
SET @monthOfThisMonth = DatePart(@today, "M")
SET @yearOfThisMonth = DatePart(@today, "Y")
IF _messagecontext == "PREVIEW" THEN
/* Use format from Business Unit */
SET @firstDayOfThisMonth = DateParse(Concat("01.", @monthOfThisMonth, ".", @yearOfThisMonth))
ELSE
/* Use format from user */
SET @firstDayOfThisMonth = DateParse(Concat(@monthOfThisMonth, "/01/", @yearOfThisMonth))
ENDIF
SET @lastMonth = DateAdd(@today, -1, "M")
SET @monthOfLastMonth = DatePart(@lastMonth, "M")
SET @yearOfLastMonth = DatePart(@lastMonth, "Y")
IF _messagecontext == "PREVIEW" THEN
/* Use format from Business Unit */
SET @firstDayOfLastMonth = DateParse(Concat("01.", @monthOfLastMonth, ".", @yearOfLastMonth))
ELSE
/* Use format from user */
SET @firstDayOfLastMonth = DateParse(Concat(@monthOfLastMonth, "/01/", @yearOfLastMonth))
ENDIF
set @lastDayOfLastMonth = DateAdd(@firstDayOfThisMonth,-1,"D")
SET @fromDate = FormatDate(@firstDayOfLastMonth, "yyyy-mm-ddTHH:mm:ss.000Z")
SET @toDate = FormatDate(@lastDayOfLastMonth, "yyyy-mm-ddT23:59:59.999Z")
]%%Essentialy what this code does is checking the context of the message using the “_messagecontext” personalization string. Based on the value of it a different date format is passed to the DateParse function.
However, this can be prone to errors as the setting of the user might change or the email including this code might be used by another user.
The more static approach
If this doesn’t work for you or is too unsafe to use as it may result in unexpected behavior, you could also build up your dates in a rather static way. At least this worked for my use case as I’m only interested in the first and last day of the month. I usually refrain from using such approaches, but in that case I couldn’t yet find a dynamic solution that takes into consideration which date format is the correct one.
%%[
SET @today = Now()
SET @monthOfThisMonth = DatePart(@today, "M")
SET @yearOfThisMonth = DatePart(@today, "Y")
SET @lastMonth = DateAdd(@today, -1, "M")
SET @monthOfLastMonth = DatePart(@lastMonth, "M")
SET @yearOfLastMonth = DatePart(@lastMonth, "Y")
VAR @daysOfLastMonth
IF @monthOfLastMonth == "02" THEN
/* Check if the year is a leap year */
IF Mod(@yearOfLastMonth,400) == 0 OR (Mod(@yearOfLastMonth, 4) == 0 AND Mod(@yearOfLastMonth, 100) != 0) THEN
/* Leap Year */
SET @daysOfLastMonth = "29"
ELSE
/* NOT a Leap Year */
SET @daysOfLastMonth = "28"
ENDIF
ELSEIF @monthOfLastMonth == "01" OR @monthOfLastMonth == "03" OR @monthOfLastMonth == "05" OR
@monthOfLastMonth == "07" OR @monthOfLastMonth == "08" OR @monthOfLastMonth == "10" OR @monthOfLastMonth == "12" THEN
SET @daysOfLastMonth = "31"
ELSE
SET @daysOfLastMonth = "30"
ENDIF
SET @fromDate = Concat(@yearOfLastMonth, "-", @monthOfLastMonth, "-01T00:00:00.000Z")
SET @toDate = Concat(@yearOfLastMonth, "-", @monthOfLastMonth, "-", @daysOfLastMonth, "T23:59:59.999Z")
]%%If you have a better solution for this issue and want to share it with the Marketing Cloud community, please feel free to contact me and I’ll happily include it in this post. Thank you!
Further Information
What are the possible values of _messagecontext in Salesforce Marketing Cloud?
- SEND - Display the rendered final message for sending to subscriber
- PREVIEW - Display the send preview options available within editor
- VAWP - Display content (in the browser via View-as-WebPage link)
- VIEWSENT - Display the non-subscriber link to preview content
- FTAF - Display the rendered Forward To a Friend message
- LANDINGPAGE - Display a landing page or microsite
- VALIDATION - Display information corresponding to the validate option in Marketing Cloud
- LINKRESOLUTION - Display resolved dynamic script at click time
- SMS - Display SMS message content
- SOCIAL - Display Social Forward content
- SITE - Display CloudPages content