-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:
- Determine year of the event
- Determine first Sunday of April of event year
- To get to the last Sunday of March of event year, which is the start of DST in Europe
- Similarly for the end of DST in October
- 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")
]%%
