0

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.

  • You can first transfer a sql date to a specific timezone, i attached an url to a case. after you changed the timezone you can then use the convert function. salesforce.stackexchange.com/questions/250864/… or in code it may look like this (but for german 104 style. CONVERT(CHAR(10),c.EventDate AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'Central European Standard Time',104) Also watch this if you dont know the 104 docs.microsoft.com/en-us/sql/t-sql/functions/… – Johannes Schapdick May 8 '19 at 8:54
  • You would like to have the 127 instead of 104, and of course you would then not use char(10) because something of particular length or even another data type – Johannes Schapdick May 8 '19 at 9:02
  • @JohannesSchapdick put that as an answer for credit. – Tom Callahan May 8 '19 at 21:59
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.