ISO8601 Z conversion to local date (Pacific) within SQL

Question

I can't figure out how to take this ISO8601 date and convert it to local time (PDT).

I've tried a couple things:

convert(datetime, updated_at_text, yyyy-mm-ddThh:mi:ss.mmmZ) as LastUpdated,

convert(datetime, updated_at_text, yyyy-mm-ddThh:mi:ss.mmmZ-0700) as LastUpdated,

cast(updated_at_text as DATETIME) as LastUpdated,

cast(updated_at_text as DATETIME) as LastUpdated,

The updated_at_text lives in an ISO format 2019-05-08T02:47:05Z The convert functions throw an error & the cast functions don't change the time to Pacific timezone.

Answer 1

At first you should transfer the sql-date to the specific timezone you want that date to be in. I had a question where I wanted to achieve this transformation. A detailled post has been published after I found that the t-sql function AT TIME ZONE is available on the stack where our business units are (S10).

Below you can find a code that I use to transfer the timestamps from marketing clouds timezone (CET) to 'Central European Standard Time'. Furthermore I converted this value to a string and chose the 104 Time style which is the german one. You can find more details about Time Styles on Microsoft Documents.

CONVERT(CHAR(10),c.EventDate AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'Central European Standard Time',104)

Depending on your choice of timezone and your choice of the datatype for the date in the target data extension you would change the code on top. The time style for ISO8601 with timezone is 127.