2

I just found a big problem in the dates I pulled from service cloud. In summary I sync one object througth service cloud however the fields dates have the wrong information. e.g in service cloud I have 10/03/2018:00:00 but in marketing cloud shows up 3/9/2018 6:00:00 PM

I already search about this and I found it:

https://help.salesforce.com/articleView?id=mc_cab_data_integration_for_your_account_with_synchronized_data_sources.htm&type=5

In my case I will need to send an email for all the clients near to pay bill.. With this If make a simply query for my DE I will get the wrong clients. Should I change my query to add first the difference of the date? That will be the right sollution? I'm very confused I 'm sorry

Thanks a lot

1

If you are on S10 and S7, MC supports the 'AT TIME ZONE' function, you can use to convert to CST to the time zone you need.

e.g

SELECT EventDate 
AT TIME ZONE 'Central Standard Time'
AT TIME ZONE 'Aus Eastern Standard Time' as date_aest

Find out more into this article https://www.linkedin.com/pulse/its-timezone-finally-real-convert-function-salesforce-jack-hobbs/

0

This shouldn't be a problem. SFDC stores the data as GMT and it seems MC stores it as CST so they automatically convert it for you. It technically still represents the exact same date/time in the real world. Furthermore, when users view the records, the UI converts it again from CST into that user's time zone. This means that the user see's a date that has real meaning to them.

The blurb in the link about needing to manually offset:

Ensure that you use the proper offset when manipulating date and time information synchronized from the Sales Cloud to the Marketing Cloud.

This is just warning you that it is stored in CST in marketing cloud in the event that you are manipulating that data after it gets to MC. It wants you to know that it is NOT stored in GMT as it is on SFDC and wants to make sure that any data manipulation processes you use, take this into account.

I've had a few projects centered around timezones and I've had to explain this a few times. SFDC is doing all of the work for you. You shouldn't have to do too much when it comes to timezone conversions.

  • Hi gNerb, thanks for your help.. but if I understood right the only thing I need to do is guaraente I have the right time zone in marketing cloud, right? If I go to the Adminstration> and take a look at my time zone configur I will see Time Zone (GMT) Dublin, Edinburgh, Lisbon, London * with this I should be able to see the same date I have in service cloud but this doesn´t happen. Could you please see If I'm missing something else? Thanks for your time – user57099 May 30 '18 at 21:38
  • sorry for my insistence, but in my case I will need to send an email for all the clients near to pay bill.. With this If make a simply query for my DE I will get the wrong clients. Should I change my query to add first the difference of the date? That will be the right sollution? I'm very confused I 'm sorry – user57099 May 30 '18 at 22:23
  • How do you know you are getting the wrong clients? – gNerb May 30 '18 at 22:31
  • Hi @gNerb, now I'm sure something is wrong.. In service I have for instance a client with date 14/06/2018 when I pull this data to marketing cloud I get 13/06/2018 and the I need query the records I get from service and find what are in this condiction convert(date,MYFIELD) = convert(date,DATEADD(DAY, 10, GETDATE())). like I said I have in service one record with 14/06 but because it change my date for 13/06 when I make this query the record doesn´t are found. How I should work? – user57099 Jun 4 '18 at 13:52
  • What I'm trying to explain to you is that while the numbers are different, those both represent the exact same time. You're also making the mistake of comparing apples to oranges (sf to mc). Additionally, since you're working on renewals, your process should be running at least daily meaning that if it's not picked up today it's picked up tomorrow which is likely correct anyway. If you want to get REALLY specific about it, instead of using days, use hours and run the process hourly instead of daily. Otherwise, your question has already been answered. I'm sorry if you don't like the answer. – gNerb Jun 4 '18 at 18:43
0

it's never a good idea to just add an offset programmatically. there's so many issues that can arise from this!

the link you sent mentions that the date is converted to cst in marketing cloud. so you'll want to convert the date to the proper timezone.

  • Ok hackerhasid, thanks for your advice. But I have my user using the correct timezone and now I need to pick data using date in my filter however with this differences I get wrong results, because in marketing cloud the date is different than service cloud. What I'm missing or didn´t understand so that can´t happen? – user57099 May 30 '18 at 21:57

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.