a PI server that is configured to use non-US date formats
What do you mean by that exactly? Can you be a little more specific about the issue you are observing?
Essentially, the OLEDB COM Connector should be able to interpret DATETIME fields appropriately, regardless of their format. Important to note too, is that if the OLEDB data source and the PI Server are not in the same time zone, all timestamps retrieved via the COM connector will reflect the time zone of the data source, not the PI System.
The COMCtr was installed on a machine configured for UK format dates DD/MM/YYYY. SQL server, which the COMCtr was querying, expected dates in MM/DD/YYYY (even though installed on same machine). When I queried archive values with either a start time or end time the COMCtr returned an error.
Tech Support in the US were unable to reproduce the problem so I'm guessing that the PI server there was on a machine which defaulted to US format dates. Reckon we could have replicated the problem by changing the identity in the DCOM settings in Component Services for the COMCtr to a specific account and then modifying the date format settings for that account. ???
Ultimately I fixed the problem by adding the following section to the INI file.
TimeFormat = "hh':'mm':'ss tt"
DateFormat = "MM'/'dd'/'yyyy"
Perhaps this should be added to the example SQLServer ini file installed with the COMCtr? Just a suggestion.
Glad you sorted this out! Thanks for sharing the solution!
You could also try to always pass dates in the yyyy/mm/dd format. That is perhaps the safest format when dealing with systems that use different regional date formats. In this format, systems always interpret the middle part of the date as being the month, not the day.