We are able to extract data from the Informix database as long as the pull is working on recent data, for example:
FROM OPENQUERY (uccx_node1, 'SELECT * FROM contactcalldetail WHERE extend ( startdatetime, year to minute ) - 5 units hour > DATE(CURRENT)-1 AND extend ( startdatetime, year to minute ) -5 units hour < DATE(CURRENT)')
The challenge I'm having is getting data that was missed for whatever reason - I need to specify a date range and based on research here and other places, came up with this:
FROM OPENQUERY (uccx_node1, ''SELECT * FROM contactcalldetail WHERE extend ( startdatetime, year to minute ) - 5 units hour > ''''' + convert(varchar, @fromdt, 121) + ''''' AND extend ( startdatetime, year to minute ) -5 units hour < ''''' + convert(varchar, @EndDate, 121) + ''''' '')'
EXEC(@query)
These are the variables:
DECLARE @fromdt DATETIME = '2024-02-20 00:00:01.001';
DECLARE @EndDate DATETIME = '2024-02-20 23:59:59.999';
When I run this, I get an error:
[Informix][Informix ODBC Driver] > [Informix]Extra characters at the end of a datetime or interval
The SQL looks fine to me:
An error occurred while preparing the query " SELECT * FROM contactcalldetail WHERE extend ( startdatetime, year to minute ) - 5 units hour > '2024-02-20 00:00:01.000' AND extend ( startdatetime, year to minute ) -5 units hour < '2024-02-21 00:00:00.000' " for execution against OLE DB provider "MSDASQL" for linked server "uccx_node1".