Por que a comparação de datas não está funcionando nesta consulta SQL?

Isso parece um problema de esquema, onde o insurance_cancellation_dté definido incorretamente como um int grande.

Com isso em mente, a melhor solução é consertar os dados (porque o que você realmente tem seria considerado quebrado ). Mas entendo que isso nem sempre é possível em um sistema existente.

Com isso em mente, não posso ter certeza da solução exata sem ver uma amostra de como são os dados reais, mas imagino que as linhas comentadas provavelmente se pareçam mais com isto:

and i.insurance_cancellation_dt >= 20250101
and i.insurance_cancellation_dt <  20250401

Observe a mudança no operador para a primeira expressão de desigualdade. O operador original parecia errado e provavelmente deveria ser usado >=em vez de >(mas o operador <rather than <=provavelmente está correto).

Além disso, vejo isto:

and c.reference='100981'

Não acho que essa seja a causa do seu erro, e normalmente eu o consideraria normal, mas como potencialmente temos outras situações de incompatibilidade de tipos na consulta, pode haver algo aqui que valha a pena abordar. Se c.referencefor, na verdade, um tipo numérico de algum tipo, em vez de um tipo string/varchar, as regras de precedência de tipos do SQL Server podem exigir a conversão de TODOS os c.referencevalores para varchar, em vez de converter '100981'para o tipo numérico uma vez. Seria necessário fazer isso para cada linha , mesmo para valores que não correspondam à expressão... porque, de que outra forma, saberia se o valor correspondeu ou não? Pior ainda, isso impede o uso de qualquer índice que você possa ter para esta coluna.

Agora temos esta nova informação:

insurance_cancellation_dté uma data e hora ... Também funciona se eu remover i.asset_hdr_id=ah.asset_hdr_ide manter a comparação de data

É estranho que a remoção de expressões condicionais resolva o problema (daí: uma postagem no Stack Overflow 😉). Se houvesse um problema de conversão de tipo em outro lugar, mais expressões condicionais tenderiam a reduzir o tamanho do conjunto de resultados, o que poderia ajudar a evitar o erro, enquanto a remoção de uma expressão tenderia a adicionar linhas e, portanto, aumentar a chance de erro. Isso faz o oposto.

Ainda assim: o otimizador de consultas pode fazer coisas estranhas. Por exemplo, adicionar uma expressão pode levar a percorrer um índice estreito em vez de buscar em uma tabela mais ampla, ou vice-versa. Dessa forma, adicionar a expressão pode significar que você visualiza mais linhas, mas tem menos leituras de página. Há outros motivos pelos quais isso também pode gerar resultados estranhos.

O certo é que, em algum lugar, isso está causando uma conversão de um tipo de dado ( nvarchar) para outro ( bigint) que você não pretendia. Prestar mais atenção aos tipos de dados de coluna e literais permitirá que você evite isso.

Vale ressaltar também que não há literais " nvarcharou bigint" em nenhuma parte da consulta. nvarcharprecisaria de um Nprefixo, e não há nada numérico grande o suficiente para bigint. Também não há operações de conversão na SELECTcláusula. Portanto, DEVE ser uma conversão ocorrendo dentro de uma expressão condicional entre duas colunas em uma cláusula " ONou WHERE". Eu prestaria mais atenção a estas três expressões:

acf.reference = i.asset_hdr_insurance_id
iStatus.xt_lookupset_id = i.insurance_status
iType.xt_lookupset_id = i.insurance_type

Onde for possível, uma das colunas insurance_status, insurance_type, ou acf.referencecontém principalmente strings que representam valores de ID nas tabelas pareadas, mas são definidas para nvarchartambém permitir uma gama mais ampla de informações de status/descritivas em algumas linhas. Lembre-se: strings que representam valores de ID não são a mesma coisa que conter os valores de ID diretamente.

É muito provável que esse problema ocorra porque o SQL Server está interpretando i.insurance_cancellation_dtcomoNVARCHAR (string), não como DATE.

Abaixo estão as correções:

1). Converta a coluna para DATEa condição :

E CAST(i.insurance_cancellation_dt COMO DATA) > '2025-01-01'

E CAST(i.insurance_cancellation_dt COMO DATA) < '2025-04-01'

2): Corrigir o tipo de coluna (ideal):

Verifique o tipo de dados real de i.insurance_cancellation_dt:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'asset_hdr_insurance'
   AND COLUMN_NAME = 'insurance_cancellation_dt';

O erro fornecido parece ocorrer quando você adiciona ( e porque o servidor pode i.insurance_cancellation_dt > '2025-01-01'reordenar) internamente as etapas de execução (junções, filtros) e tentar corresponder uma coluna a uma na linha (se esses tipos não corresponderem).nvarcharbigintacf.reference = i.asset_hdr_insurance_id

Espero que esta solução ajude!

Acho que é por causa de c.references='100981'. Por causa do , ''ele será interpretado como string (nvarchar).

Você já tentou isso? :

 select c.reference AccountNo,
        i.insurance_cancellation_dt CustomerCancelDte,
        cus.ext_name CustomerName,
        case when f.amt_matched=0 
            then 'Paid' 
            else 'Unpaid' 
        end ChargebackStatus,
        '' DlrCancelRequiredInd,
        acf.reference NetcheckAccountNo,
        iType.value ProductDesc,
        acf.amount TotRefundAmt,
        iParty.ext_name VendorName,
        v.vin_no Vin
from contract c 
inner join asset_hdr ah on c.contract_id = ah.current_contract_id 
and c.dealer_id = 1201 
and c.reference = 100981
inner join asset_hdr_insurance i on i.asset_hdr_id = ah.asset_hdr_id