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
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?