0

In Salesforce Marketing Cloud, we are importing order data into a DE to drive a live, in-person, event registration confirmation email. This data includes the time of a given event in UTC format so that it can be displayed in the content. However, we need some way to convert the UTC from the DE to the local event time (US: ET, CT, PT) and also be able to account for the variations in Daylight Savings Time so that the accurate local time at the event site is what displays in the content.

Is there anything with SF MC or any AMPscript coding that can accomplish this?

This article comes close but does not address the DST piece

https://www.linkedin.com/pulse/its-timezone-finally-real-convert-function-salesforce-jack-hobbs/

I did not see this specific use case in any previously asked questions - apologies if I missed it.

Thanks, Dennis

1 Answer 1

1

The 'AT TIME ZONE' SQL function does account for Daylight Savings time automatically. So if your SFMC Stack accepts this SQL function, then you have your full solution there.

Source 1:

When the clock's set ahead, there's a gap in local time equal to the duration of the clock adjustment. This duration is usually 1 hour, but it can be 30 or 45 minutes, depending on time zone. Points in time that are in this gap are converted with the offset after DST change.

/*
  Adjusted time from the "gap interval" (between 02:00 and 03:00)
  is moved 1 hour ahead and presented with the summer time offset
  (after the DST change) 
*/
SELECT CONVERT(datetime2(0), '2015-03-29T02:01:00', 126)   
AT TIME ZONE 'Central European Standard Time';  
--Result: 2015-03-29 03:01:00 +02:00

--Time after 03:00 is presented with the summer time offset (+02:00)
SELECT CONVERT(datetime2(0), '2015-03-29T03:01:00', 126)   
AT TIME ZONE 'Central European Standard Time';  
--Result: 2015-03-29 03:01:00 +02:00  

When the clock is set back, then 2 hours of local time are overlapped onto one hour. In that case, points in time that belong to the overlapped interval are presented with the offset before the clock change:

/*
  Time from the "overlapped interval" is presented with standard time 
  offset (before the change)    
*/
SELECT CONVERT(datetime2(0), '2015-10-25T02:00:00', 126)   
AT TIME ZONE 'Central European Standard Time';  
--Result: 2015-10-25 02:00:00 +02:00  


--Time after 03:00 is regularly presented with the standard time offset (+01:00)    
SELECT CONVERT(datetime2(0), '2015-10-25T03:01:00', 126)   
AT TIME ZONE 'Central European Standard Time';  
--Result: 2015-10-25 03:01:00 +01:00

Source 2:

So AT TIME ZONE makes it easy to convert between time zones – but how does it handle daylight savings time conversions?

Well, on March 11, 2018 I lost an hour of my life when daylight savings time kicked in at 2am. Does AT TIME ZONE recognize this theft?

YES! Say so long to conditional conditions that subtract an amount of time based on the date; AT TIME ZONE handles the switch to DST with ease as noted by the offset change from -05:00 to -04:00 .

And just like that, programming for time zone and day light savings time logic just became a little easier.

4
  • This validates in SFMC, but does not seem to actually work... Jun 11, 2020 at 18:34
  • I just verified again in my account that using AT TIME ZONE works. What issue are you running into that it does not work for you? Jun 12, 2020 at 13:39
  • To verify, you need to first set the existing time zone, then the one you want to translate to. E.g. AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Aus Eastern Standard Time' to get Australian Eastern Standard Time output. Jun 12, 2020 at 13:40
  • 1
    I ended up using ampscript format and add() instead, but I'll give that a try as well. Jun 12, 2020 at 20:55

You must log in to answer this question.

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