We recently ran into an issue in our Starfish ETL data integrations between a back-end ERP system and Infor CRM. We noticed that Date/Time fields were imported successfully via the OLEDB provider but displayed incorrectly inside the CRM application.
Infor CRM was doing the GMT adjustment to the Date/Time field correctly, so the problem could not be traced to faulty CRM functionality. Instead, this meant the initial data must have been incorrect.
This is what we expect to see, ideally:
- Date/Time supplied to Infor CRM is 01/01/2020 00:00
- Infor CRM saves this as 01/01/2020 06:00 AM (adjusted to GMT)
- Then when displaying information, Infor CRM re-adjusts the time to the original time zone and subtracts 6 hours (for Central Time) to show it as 01/01/2020
We found that step #2 was not happening. After troubleshooting further, we found we could solve the issue if the supplied query was a parameterized one versus what we were using (non-parameterized).
So the solution was to have Starfish ETL supply the update queries in a parameterized form, which can be accomplished by setting the UseParamQueries to True in the Web.Config file.
Once this change was made, everything worked perfectly!