使用SQL Server OpenQuery从Informix取数时的日期格式错误解决

问题描述 #

从Informix数据库提取近期数据的静态SQL可正常执行：

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)')

但使用动态SQL提取指定日期范围数据时，触发错误：

[Informix][Informix ODBC Driver] > [Informix]Extra characters at the end of a datetime or interval

动态SQL及变量定义如下：

DECLARE @fromdt DATETIME = '2024-02-20 00:00:01.001';
DECLARE @EndDate DATETIME = '2024-02-20 23:59:59.999';

DECLARE @query NVARCHAR(MAX) = N'
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)

生成的SQL语句看似正常，但仍报错：

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".

错误原因 #

Informix对datetime类型的精度匹配要求严格：

• 静态SQL中，extend(startdatetime, year to minute)将时间截断到分钟精度（无秒、毫秒），和DATE(CURRENT)的精度一致，可正常比较。
• 动态SQL中，convert(varchar, @fromdt, 121)生成的字符串包含毫秒（如'2024-02-20 00:00:01.000'），但比较左侧是分钟精度的datetime，Informix解析时判定字符串存在多余字符，触发报错。

解决方案 #

确保比较两侧的datetime精度完全一致，以下两种方案均可解决问题：

方案1：截断日期变量到分钟精度 #

将SQL Server日期变量转换为仅含年-月-日 时:分的字符串，与extend(startdatetime, year to minute)的精度匹配：

DECLARE @fromdt DATETIME = '2024-02-20 00:00:01.001';
DECLARE @EndDate DATETIME = '2024-02-20 23:59:59.999';

-- 转换为分钟精度字符串（格式：yyyy-mm-dd hh:mi）
DECLARE @fromdt_str VARCHAR(16) = CONVERT(VARCHAR(16), @fromdt, 120);
DECLARE @EndDate_str VARCHAR(16) = CONVERT(VARCHAR(16), @EndDate, 120);

-- 拼接动态SQL，确保引号转义正确
DECLARE @query NVARCHAR(MAX) = N'
SELECT * FROM OPENQUERY (uccx_node1, ''SELECT * FROM contactcalldetail 
WHERE extend(startdatetime, year to minute) - 5 units hour > ''''' + @fromdt_str + ''''' 
AND extend(startdatetime, year to minute) - 5 units hour < ''''' + @EndDate_str + ''''' '')
';

EXEC(@query);

方案2：调整Informix查询的精度到秒级 #

将extend(startdatetime, year to minute)改为extend(startdatetime, year to second)，与带秒的日期字符串精度匹配（去掉毫秒即可）：

DECLARE @fromdt DATETIME = '2024-02-20 00:00:01.001';
DECLARE @EndDate DATETIME = '2024-02-20 23:59:59.999';

-- 转换为秒精度字符串（格式：yyyy-mm-dd hh:mi:ss）
DECLARE @fromdt_str VARCHAR(19) = CONVERT(VARCHAR(19), @fromdt, 120);
DECLARE @EndDate_str VARCHAR(19) = CONVERT(VARCHAR(19), @EndDate, 120);

DECLARE @query NVARCHAR(MAX) = N'
SELECT * FROM OPENQUERY (uccx_node1, ''SELECT * FROM contactcalldetail 
WHERE extend(startdatetime, year to second) - 5 units hour > ''''' + @fromdt_str + ''''' 
AND extend(startdatetime, year to second) - 5 units hour < ''''' + @EndDate_str + ''''' '')
';

EXEC(@query);

额外注意事项 #

• 动态SQL引号拼接易出错，建议先执行PRINT @query验证生成的SQL格式，确认无误后再执行。
• 若需保留毫秒精度，需将Informix的extend调整为year to fraction(3)，同时日期变量转换为带毫秒的格式（如121格式），确保两边精度完全一致。

内容的提问来源于stack exchange，提问作者craig.white