2

I'm using a query activity to populate a data extension with send results for individual messages. Here's my query:

SELECT
    l.UniqueId,
    s.EventDate,
    convert(varchar, s.EventDate, 127) AS EventDateISO
FROM
  _Sent s
JOIN
   SendLog l ON l.JobID = s.JobID AND l.BatchID = s.BatchID AND l.SubID = s.SubscriberID

Here are some sample records from the target data extension:

b1646f89-a0f5-4dcb-9f3a-588b75884d49     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050
b0a97909-9b7b-4147-8b9f-87c7ac9c1059     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050
0825eda8-6908-46b1-b8ea-049c3c4c6b2b     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050
6cfe53f4-e8f1-47b4-a5b7-c8a0266156d8     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050
823dd0aa-caef-4d9b-80a9-1b20e5325d72     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050
af335c2b-b48f-4efe-976b-d84341da7801     2/24/2015 2:49:49 PM    2015-02-24T14:49:49.050

My problem is that the send occurred at 3:49 PM EST. Neither _Sent.EventDate nor the ISO-ish conversion of _Sent.EventDate provides sufficient information to determine the actual date. If it was UTC, the data would look like this:

b1646f89-a0f5-4dcb-9f3a-588b75884d49     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050
b0a97909-9b7b-4147-8b9f-87c7ac9c1059     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050
0825eda8-6908-46b1-b8ea-049c3c4c6b2b     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050
6cfe53f4-e8f1-47b4-a5b7-c8a0266156d8     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050
823dd0aa-caef-4d9b-80a9-1b20e5325d72     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050
af335c2b-b48f-4efe-976b-d84341da7801     2/24/2015 8:49:49 PM    2015-02-24T20:49:49.050

So my question is, must I make the assumption that all EventDate values are actually in Central Standard Time or maybe Central Daylight Time depending upon when the event occurred? I've seen comments in other posts that SalesForce stores every date in UTC, but this doesn't seem to be the case for MC.

2

MC stores all dates in CST all year round, so yes please make that assumption, even if you change the settings on your account to reflect a different timezone.

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