0

When working with Marketing Cloud Connect, is there anything in particular you should be mindful of, when working with date/time fields?

1 Answer 1

1

Yes there is - in my example, I'm based out of Denmark, which means I am in GMT+1, but more over I am also in a country that has summer and winter time - which poses a challenge.

The key observation:

  • The time that is written in a data extension created by a Salesforce Entry Event used in Journey Builder
  • is not the same
  • as the time written for the same record into a Synchronized Data Extension

For example:

  • An event in Salesforce is scheduled to be 10.00 AM local time
  • This comes over at 08.00 AM in the SF Entry Event DE (due to integration users locale)
  • And the same event comes over as 02.00 AM in the Synchronized Data Extension, which is corresponding to the server / system time in Marketing Cloud

Luckily, there is an easy way to overcome this using AMPscript (and note, you need ampscript to correctly portray date/time originating from within Salesforce)

You need two things:

  • A data extension (Time_Offset)
  • Some AMPscript


The data extension
This is configured with 3 fields:

  • Locale (Text, 10) : This is the primary key
  • Adjustment (Number) : This is where you enter the adjustment
  • Explanation (Text, 150) : Used to explain what the line represents

As I am only creating a solution for Denmark, I only have 2 records in here, but you can have as many as you need. They look something like this:

DK-trig | 2 | This is used for SF triggered sends, write 2 in summer time, 1 in winter
DK-sync | 8 | This is used for date-time from SF Sync DE's, write 8 in summer time, 7 in winter


The associated script
Below is a piece of ampscript you can use to pull in fields an adjust the time.

The first script is for modifying date/time when pulling from a synchronized data extension.

%%[
SET @EventDE = "ent.Event_Salesforce"
SET @EventID = EventId /*You need the ID to pull right record from ent.Event_SalesforcE */ 

    SET @EventRows = LookupRows(@EventDE, "Id", @EventID)
    IF RowCount(@EventRows) > 0 THEN
        SET @Event = Row(@EventRows, 1)
        SET @SystemEventDate = Field(@Event, "StartDateTime")
        SET @TimeAdjustment = Lookup("Time_Offset", "Adjustment", "Locale", "DK-sync")
        SET @EventDate = DateAdd(@SystemEventDate, @TimeAdjustment, "H")
            SET @WeekDay = FormatDate(@EventDate, "ddddd", "", "da")
            SET @StartDate = FormatDate(@EventDate, "dd/MM-YY", "")
            SET @StartTime = FormatDate(@EventDate, "", "HH:MM")
    ENDIF
]%%
Your meeting will be held %%=v(@WeekDay) the %%=v(@StartDate)=%% at %%=v(@StartTime)=%%

The second piece of code is for when you are modifying a date/time field located in your data extension (typically for use with Journey Builder)

%%[
SET @SystemEventDate = Event:StartDateTime
SET @TimeAdjustment = Lookup("Time_Offset", "Adjustment", "Locale", "DK-trig")

SET @EventDate = DateAdd(@SystemEventDate, @TimeAdjustment, "H")
    SET @WeekDay = FormatDate(@EventDate, "ddddd", "", "da")
    SET @StartDate = FormatDate(@EventDate, "dd/MM-YY", "")
    SET @StartTime = FormatDate(@EventDate, "", "HH:MM")
]%%
Your meeting will be held %%=v(@WeekDay) the %%=v(@StartDate)=%% at %%=v(@StartTime)=%%

Hopefully, this will save some, some hairpulling when working with similar scenarios

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .