7 Replies Latest reply on Aug 1, 2019 6:43 PM by HongTai

    Date fields on contact record are a day behind our CRM after daily SFTP of data from CRM - anyone else have this issue?


      We have several date fields on our Eloqua contact record and they're all a day behind as compared to our CRM. They're data type "Date'. Example, the birth date in a contact record is 6/25/19 but our CRM is 6/26/19. This date is entered into the CRM by users all over the United States (EST, CST, MST, PST). The Eloqua import data file is created in our CRM system at 2:30 AM CST daily then Eloqua automatically picks up this SFTP file at 4:00 AM CST daily. The dates in the SFTP file don't include a timestamp and when they're added to the contact record by Eloqua I don't see a a timestamp in the field, just the date. Surely Eloqua adding a timestamp to dates must have caused other Eloqua clients an issue so we're looking for a solution that will work for us. Having our development team add a timestamp during the creation of the Eloqua import file isn't preferable.  Any ideas are appreciated.


      A BIT OF HISTORY: Oracle believed the display of the date was a result of the timezone configured in our instance being UTC-6 Central Time and said regardless of the time the data is entered into the system the timestamp will always be set to midnight (12:00AM) of the day in question. The data viewed in Eloqua reflects the timezone of the user viewing the data. Example, when they logged in as an Eloqua user set to EST they saw the birth date set to 9/24/1955 12:00 AM. When they switched to our user account with the timezone of CST and logged in as an Eloqua user they saw the birth date set to 9/23/1955 11:00 PM which reflects the one hour difference between EST and CST. They said regardless of the time the data is entered into the system, the timestamp will always be set to midnight. We asked that a request be entered to correct this in a future release since it adversely affects the selection of data into Eloqua our segments. We were told there were existing Enhancement Requests for a future release but they might not be considered for inclusion so we were given a resolution to use an excel file to convert the time before the file is uploaded into Eloqua. This isn't possible due to the creation and SFTP of the file being automated and the file not being accessible to us for security reasons. The Oracle development team investigated the issue and found that the user timezone that was tested with wass set to CST and the examples were showing properly the day before at 11:00 PM. They stated if we set the user timezone to EST we would see the date as 12:00 AM. They have stated that to resolve this we should include a time of 12:00 PM to ensure the date doesn't change in Eloqua for users with a CST timezone. Having said that, one month ago we worked with Oracle Export Services to create a birth date "correction" program (canvas) which they believed would allow us to correct the birth date after the SFTP file was imported into Eloqua from our CRM every day, and hopefully we would also be able to correct all the past incorrect birth dates in bulk. This meant a "fix" requiring manual implementation by us. We created the correction program to add every contact through it using a segment and tested it. The contact washing machine checked for a birth date ending in 23:00 and set a flag to "Y" if it met this condition. If the flag wasn't "Y" the contact was added to a Shared List as not needing to be updated.  If the flag was "Y" then 3 hours were added to the birth date. We also tried checking for 24:00. However, the results were always inconsistent.