DateTime is stored in SFDC as UTC, then sent over to SFMC via Connector and is converted to Central Time - need to reconvert back to UTC

Question

I have implemented Connector between SFMC and Sales Cloud. The date/time values that are brought over with various objects, when they arrive in SFMC, they seem to be converted to CST (SFMC servers are CST so this makes sense). And previous research indicates that this is a common, expected behavior - SFMC converts UTC time to CST when it arrives to SFMC. What I'm noticing is after converting it back to UTC (first column below), it's one hour off from what is in Sales Cloud. Sales Cloud has a UTC time of 12:25pm. When I perform the below SQL logic, it converts to 11:25AM which not only is one hour off, it's a 5 hour difference from UTC. CST is I believe a 6 hour offset. I can, however, change out "Central Standard Time" with "Central America Standard Time" to get the correct -6 hour offset, but I don't know if that is the right path to go.

Here's my query:

select
    CONVERT(DATETIME, CONVERT(DATETIMEOFFSET, CONVERT(DATETIMEOFFSET, sa.SchedStartTime__c AT TIME ZONE 'Central Standard Time') AT TIME ZONE 'UTC')) as SchedStartTimeUTC,
    sa.SchedStartTime__c as SchedStartTimeSFMC,
    CONVERT(DATETIME, CONVERT(DATETIMEOFFSET, CONVERT(DATETIMEOFFSET, sa.SchedStartTime__c AT TIME ZONE 'Central Standard Time') AT TIME ZONE 'UTC')) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' as EasternStandardTime
from
    ent.Contact_Salesforce contact with (nolock)
inner join
    ent.VMS_ServiceAppointment__c_Salesforce sa with (nolock) on sa.Contact__c = contact.Id

Here's the result set:

The first column, SchedStartTimeUTC is the result of converting the SFMC changed time back to UTC. Notice it's 11:25AM and not 12:25PM which it should be. The second column is the value SFMC receives after SFMC adjusts the time to CST. The last column, I'm just trying to convert the overall result to EST.

Question: why does it appear that when I convert from CST to UTC, it only has a 5 hour offset and not the expected 6 hour offset?

Answer 1

The reason for your issue is that AT TIME ZONE converts datetime taking Daylight Saving Time into account.

Salesforce Marketing Cloud indeed uses CST, but without changes between standard and daylight savings time.

This leads to the double AT TIME ZONE approach to be wrong by one hour for half a year.

You need to use TODATETIMEOFFSET and AT TIME ZONE to properly change datetimes to what you need.