SQL查询报错排查：过滤到期日为今日起30天内的INNER JOIN语句

错误原因 #

你在INNER JOIN的ON条件里只写了DATEDIFF(GETDATE(), docs.termino_vigencia)，这是不完整的条件：

• DATEDIFF函数仅返回两个日期的差值，但没有指定这个差值需要满足的范围（比如是否在0到30之间），数据库无法判断该条件的真假，直接触发语法错误。
• 同时你搞反了DATEDIFF的参数顺序：如果要计算从今日到到期日的天数差，正确参数顺序应为DATEDIFF(day, GETDATE(), docs.termino_vigencia)，原写法会在到期日为未来日期时返回负数，逻辑不符合需求。

修正后的SQL语句 #

推荐将到期日过滤条件放在WHERE子句中（逻辑更清晰），也可以放在JOIN的ON条件里，以下是两种可行写法：

写法一：条件放在WHERE子句（更推荐）

SELECT 
    clientes.nome, 
    docs.apolice, 
    docs.nome_seguradora as seguradora, 
    docs.nome_produto as ramo, 
    comissoes.premio_liqdesc as premio_liquido, 
    comissoes.premio_total as premio_total, 
    docs.inicio_vigencia as inicio, 
    docs.termino_vigencia as fim 
FROM growsystem.documentos docs 
INNER JOIN growsystem.comissoes comissoes 
    ON docs.apolice = comissoes.apolice 
    AND docs.situacao = 'Ativa' 
INNER JOIN growsystem.clientes clientes 
    ON clientes.nome = comissoes.nome_cliente
WHERE 
    docs.termino_vigencia BETWEEN GETDATE() AND DATEADD(day, 30, GETDATE());

写法二：条件放在JOIN的ON条件中

SELECT 
    clientes.nome, 
    docs.apolice, 
    docs.nome_seguradora as seguradora, 
    docs.nome_produto as ramo, 
    comissoes.premio_liqdesc as premio_liquido, 
    comissoes.premio_total as premio_total, 
    docs.inicio_vigencia as inicio, 
    docs.termino_vigencia as fim 
FROM growsystem.documentos docs 
INNER JOIN growsystem.comissoes comissoes 
    ON docs.apolice = comissoes.apolice 
    AND docs.situacao = 'Ativa' 
    AND DATEDIFF(day, GETDATE(), docs.termino_vigencia) BETWEEN 0 AND 30
INNER JOIN growsystem.clientes clientes 
    ON clientes.nome = comissoes.nome_cliente;

补充说明 #

用BETWEEN GETDATE() AND DATEADD(day, 30, GETDATE())的写法比DATEDIFF更直观，还能让数据库更好地利用docs.termino_vigencia字段上的索引（如果存在），提升查询效率。

内容的提问来源于stack exchange，提问作者Johnny Gregório