OpenQuery日期选择

Question

我有下面的openquery，它工作得很好，但现在我想使用gl_trans.gl_trans_date只选择transactions >= '2017-07-01'，但无法获得正确的语法。后台数据库是Informix，我已经通过google尝试了很多建议，但都没有成功:(

SELECT *
FROM OpenQuery(PRONTO_L01, 'SELECT 
gl_trans.gl_accountcode,
gl_trans.gl_amount,
gl_trans.gl_trans_date ,
gl_trans.gl_details
FROM gl_trans
WHERE gl_trans.gl_amount <> 0')

Answer 1

修改WHERE子句上的查询并添加新的列条件，如下所示。试试这个：

SELECT *
FROM OpenQuery(PRONTO_L01, "SELECT 
gl_trans.gl_accountcode,
gl_trans.gl_amount,
gl_trans.gl_trans_date ,
gl_trans.gl_details
FROM gl_trans
WHERE gl_trans.gl_amount <> 0 AND gl_trans.gl_trans_date >= '2017-07-01'")

Answer 2

问题是Informix日期格式为MM-DD-2017，使用日期函数解决，如下所示

gl_trans.gl_trans_date >=日期(‘07-01-2017)’“

Answer 3

     I did something like that and it worked.

    Declare @date DATETIME = '2017-07-01';
    SELECT *
    FROM OpenQuery(PRONTO_L01, 'SELECT 
    gl_trans.gl_accountcode, 
    gl_trans.gl_amount,
    gl_trans.gl_trans_date ,
    gl_trans.gl_details
    FROM gl_trans
    WHERE gl_trans.gl_trans_date  >= ''''' + 
    CONVERT(CHAR(19), @date, 120) + N'''''''))