Different time zones across different servers can be confusing. For example, most of the SFMC (ExactTarget) servers are in the Central Time Zone. Also if you’re bringing over data through the Marketing Cloud Connector, the date/time from Sales/Service/Health Cloud are all in the UTC time zone.

You may have tried using CONVERT or DATEADD in SQL to update the times to match, but with different regions with daylight savings time and others not recognizing daylight savings, the time and dates can get screwy during certain months of the year.

Use Case:

You have appointment data in Service Cloud in UTC, but your hospitals are located only on the East Coast of the United States. When you bring over data through the Marketing Cloud Connector, you want to always show that data in Eastern Standard Time.

Solution:

The solution is to use AT TIME ZONE which can consistently and accurately convert your time to a specified local time zone no matter what time of the year and whether or not the local time zone is experiencing daylight saving time. Here’s the syntax:

Syntax:

SELECT inputdate AT TIME ZONE ‘Name of destination time zone’;

Example:

SELECT appointmentTime AT TIME ZONE ‘Eastern Standard Time’ as appointmentTime_EST

The AT TIME ZONE SQL function consistently converts the time from UTC to EST in the example above. Any of the supported timezones below can be used in place of ‘Name of destination time zone’ shown in the syntax above.