AMPscript: Use SystemDateToLocalDate() to Convert a Data Extension Value to Local Time
Scenario
We have a Journey Builder Update Contact Activity writing to a DE field called JourneyEntryDate with the current system timestamp as each contact passes through this canvas activity. In a subsequent email the journey, we wish to add +14 days to this DE datetimestamp and then convert it to the local time.
PROBLEM:
It can be tricky converting a datetime field from a Data Extension to the local date using theSystemDateToLocalDate() function. Error returned “string was not recognized as a valid DateTime.”
%%=SystemDateToLocalDate(AttributeValue('JourneyEntryDate'))=%%
It won’t work because the DE data field we are trying to convert is not a “system” data (noting that SystemDateToLocalDate(Now(1)) will work because it is a system datetime object).
SOLUTION:
The workaround is to format the Data Extension datetime field to ISO value first. This will attach additional datetime details as well as format the DE date correctly making it straightforward for the computer to understand what format the date is in to begin with.
%%=Format(SystemDateToLocalDate(FormatDate(AttributeValue('JourneyEntryDate'), "iso")), "dd/MM/yyyy h:mm:ss tt", "date")=%%Without this additional information, the computer is unable to know how to format the DE date to the local date since it cannot interpret correctly which is Day and which is Month to begin with.
DEMO:
%%[
SET @de_datetime = AttributeValue("timestamp")
SET @format_iso_DE_datetime = FORMATDATE(@de_datetime, "iso")
SET @convert_iso_to_local = SystemDateToLocalDate(@format_iso_DE_datetime)
SET @format_local = Format(@convert_iso_to_local, "dd/MM/yyyy h:mm:ss tt", "date")
]%%de_datetime: %%=v(@de_datetime)=%% <br><br>
format_iso_DE_datetime: %%=v(@format_iso_DE_datetime)=%% <br><br>
convert_iso_to_local: %%=v(@convert_iso_to_local)=%% <br><br>
format_local: %%=v(@format_local)=%%
