SFMC date timezone

Question

Can we convert Dataview Sent/Open CST date to UTC timezone without using dateadd funtion. Any other timezone related function?

Answer 1

Yes, with AT TIME ZONE.

Here's an example from SFSE:

SELECT 
  t.mailingState
, v.specificUTCDateTime
, case 
    when t.mailingState = 'AB' then convert(datetime2, v.specificUTCDateTime at time zone 'MOUNTAIN STANDARD TIME')
    when t.mailingState = 'VC' then convert(datetime2, v.specificUTCDateTime at time zone 'PACIFIC STANDARD TIME')
  end adjSendDate
from timezones_canada t
outer apply (
  select
     SMALLDATETIMEFROMPARTS(YEAR(GETUTCDate()), MONTH(GETUTCDate()), DAY(GETUTCDate()), 19, 30) AT TIME ZONE 'UTC' specificUTCDateTime
) v