Correct date and time while considering Daylight Savings Time (DST) in event confirmations sent out via SFMC but created in SFSC needed

ampscriptemailmarketing-cloudsales-cloudtimezone

Some time ago I was working on some Marketing Automation improvements at a client when we encountered a very specific problem:

For appointment confirmations sent out via e-mails from SF Marketing Cloud but appointments created in Sales Cloud, we need the correct date and, especially, time while considering Daylight Savings Time (DST).

As SQL and querying in SFMC will give you more than one option to to find the correct date and time, AMPscript doesn’t seem to have those options to integrate that proper date and time in an e-mail.

I have searched Stack and other sources like Mateusz Dabrowski and SalesforceBen, but I couldn’t find the right solution for this specific issue.

The problem is that when creating an appointment/event in Sales Cloud, it shows the correct date and time, also taking DST into account. For example, if you create an event in Central European Time (CET) on February 12, 2024 at 14.00 (2PM) (create date), and the event is at June 14, 2024 at 15.00 (3PM), Sales Cloud will show you those specific dates and times as SC will handle the DST just fine in the front end.
The date and time stored in SC is on UTC, so create date will be something like 2024/02/12 13.00.00, as CET is UTC+1 in winter. The event date will be stored as 2024/06/14 13.00.00, as CET is UTC+2 in summer (DST).

The problem with sending out a confirmation with MC, it can take the event date (as stored) and you can add an hour in winter and two hours in summer, but how does the AMPscript determine if DST needs to be applied?

Some consulting parties created a big IF-statement for each year to determine if the event date is in DST and needs 2 hours added, but that means it a manual script and you have to add new dates in there all the time. We especially encountered the problem when the event date was taking place further in the future and the new dates were not added yet, resulting in a confirmation stating the wrong time, in this case June 14, 2024 at 14.00 instead of 15.00. Customers are not really happy with such annoying errors, especially when it’s about real estate.

After googling for ages, I created something based on different scripts. First of all, some recognition to @Gortonington: his site and answers here been of great help. I converted his solution of finding the first specific day in a month to finding the exact dates of any year for the start and end of DST, so that the event date and time can be adjusted accordingly.

Please upvote my question and solution if you encountered the same problem and find this solution useful, so more people can find and use it. And hopefully AMPscript gets updated to simply adjust for DST a bit easier that this solution.

Best Answer

-edit- This is the solution for EU. So I took Gortoningtons script from his answer here, altered it a bit and provide some clear (hopefully) comments to step by step go through the solution. Mainly the steps in getting the proper date and time in the email in SFMC from an event in Sales Cloud are the following:

  1. Determine year of the event
  2. Determine first Sunday of April of event year
  3. To get to the last Sunday of March of event year, which is the start of DST in Europe
  4. Similarly for the end of DST in October
  5. Adjust time in email accordingly to DST
%%[
/* Events in Sales Cloud are stored in UTC time, yet you want to show the right time in emails, e.g. confirmations, but importing a Sales Cloud event datetime and converting to the right local time can be a hassle */
/* Script below determines the period of Daylight Savings Time of the year of a Sales Cloud event and adjusting the time shown in emails of such an event (in this case an appointment confirmation) accordingly (for Amsterdam timezone) with +1 in winter and +2 hours in summer/DST */
/* Steps in this process are the following:
 1. Determine year of the event
 2. Determine first Sunday of April of event year
 3. To get to the last Sunday of March of event year, which is the start of DST in Europe
 4. Similarly for the end of DST in October
 5. Adjust time in email accordingly to DST */
 
/* Defines event date from the event in Sales Cloud */
set @eventStart =  Event:StartDateTime 

/* Grabs the year part of event date */
set @eventYear = DatePart(@eventStart,'Y')

/* Determine the Start of Daylight Savings Time of the year of the Sales Cloud event */
/* Create first of April of year of event*/
set @foApril = DateParse(Concat(@eventYear,'/','04','/','01'))

/* Grabs the day name from first of first of april */
set @foAprilDay = FormatDate(@foApril,'ddddd')

/* String created to be used as rowset to find day index to first Sunday of April of the event year - make sure to start this string with the day you need!, in this case Sunday  */
set @dayStr = "Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday"

/* Turn above string into rowset */
set @dayRS = BuildRowsetFromString(@dayStr,'|')

/* For loop to iterate through this rowset */
for @i=1 TO Rowcount(@dayRS) DO

  /* Sets row to look at based on iterative (@i) */
  set @row = Row(@dayRS,@i)

  /* Sets the field to display, in this case we use ordinal as there is no column name */
  set @day = Field(@row,1)

  /* Use a conditional to check if the days match */
  if @foAprilDay == @day then
    
    /* Once they match, you set the 'index' to equal 8 minus the iterative to calculate how many days you need to add to determine which date the first Sunday is*/
    /* Gortonington chose 8 so that if the last day (Saturday) of the rowset is equal to the first day of the specific month (in this case April 1) (and interative 7)... */
  /* ...we need to add 1 day to find the date of the first Sunday of April from the first of April */
  /* Simarly for the other days. Hint: try in Excel to understand this whole process a bit easier */
    set @index = Subtract(8,@i)

    /* Conditional that if index is greater than 6, to set to 0 */
    /* This will account for if the first is a Sunday (or the day you chose as first in your rowset) and makes sure that the date doesn't shift a week */
    if @index > 6 then
      set @index = 0
    endif

  endif

next @i

/* Add together the index to the first of April to get first Sunday of April*/
set @firstSun = DateAdd(@foApril,@index,'D')

/* Get date of last Sunday of March, which is 7 days prior to the first Sunday of April*/
set @LastSunMar = DateAdd(@firstSun,-7,'D')

/* Get Start date and time of Daylight Savings Time, which starts at 01.00 UTC of the last Sunday*/ 
set @startDST = DateAdd(@LastSunMar, 1, 'H')

/* Determine the end of Daylight Savings time of the year of the Sales Cloud event */
/* Create first of November of year of event*/
set @foNov = DateParse(Concat(@eventYear,'/','11','/','01'))

/* Grabs the day name from first of first of November */
set @foNovDay = FormatDate(@foNov,'ddddd')

/* String created to be used as rowset to find day index to first Sunday of Nov of the event year - make sure to start this string with the day you need! */
set @dayStr2 = "Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday"

/* Turn above string into rowset */
set @dayRS2 = BuildRowsetFromString(@dayStr2,'|')

/* For loop to iterate through this rowset */
for @j=1 TO Rowcount(@dayRS2) DO

  /* Sets row to look at based on iterative (@i) */
  set @row2 = Row(@dayRS2,@j)

  /* Sets the field to display, in this we use ordinal as there is no column name */
  set @day2 = Field(@row2,1)

  /* Use a conditional to check if the days match*/
  if @foNovDay == @day2 then
    
    /* Once they match, you set the 'index' to equal 8 minus the iterative to calculate how many days you need to add to determine which date the first Sunday is*/
    /* Gortonington chose 8 so that if the last day (Saturday) of the rowset is equal to the first day of the specific month (in this case November 1) (and interative 7)... */
  /* ...we need to add 1 day to find the date of the first Sunday of November from the first of November  */
  /* Simarly for the other days */
    set @index2 = Subtract(8,@j)

    /* Conditional that if index is greater than 6, to set to 0 */
    /* This will account for if the first is a Sunday (or the day you chose as first in your rowset) and makes sure that the date doesn't shift a week */
    if @index2 > 6 then
      set @index2 = 0
    endif

  endif

next @j

/* Add together the index to the first of November to get first Sunday of November*/
set @firstSunNov = DateAdd(@foNov,@index2,'D')

/* Get date of last Sunday of October, which is 7 days prior to the first Sunday of November */
set @LastSunOct = DateAdd(@firstSunNov,-7,'D')

/* Get END Date and time of Daylight Savings Time, which is again at 01.00 UTC */
set @EndDST = DateAdd(@LastSunOct, 1, 'H')

/* Determine if Event date and time is in DST period or not, and add +1 or +2 (DST) to the Sales Cloud event date and time for showing the right time in the email in Marketing Cloud*/

if @eventStart > @StartDST AND @eventStart < @EndDST then
 set @appointment = DateAdd(@eventStart, 2, 'H')
else set @appointment = DateAdd(@eventStart, 1, 'H')
endif

/* Formatting I needed (in the Netherlands) for usage in the email, so change accordingly*/
set @StartDay = FormatDate(@appointment, "ddddd", , "nl-NL") 
set @StartDate = FormatDate(@appointment, "dd MMMMM YYYY",, "nl-NL") 
set @StartTime = FormatDate(@appointment, "", "HH:MM") 

]%%
Related Topic