为何此SQL查询中的日期比较功能失效？

报错内容：Error converting data type nvarchar to bigint

运行下方SQL查询时，一旦取消注释日期比较条件就会触发上述类型转换错误，但移除这两行条件后查询可正常执行。

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
--   and i.insurance_cancellation_dt > '2025-01-01'
--   and i.insurance_cancellation_dt < '2025-04-01'
inner join 
    party cus on cus.party_id = c.cparty_id
inner join 
    asset_custom_flow acf on acf.reference = i.asset_hdr_insurance_id
                          and acf.custom_flow_hdr_id = (select custom_flow_hdr_id 
                                                        from custom_flow_hdr 
                                                        where name = 'AMP Chargeback')
inner join 
    flow f on f.contract_id = c.contract_id 
           and f.custom_flow_link_no = acf.link_no
inner join 
    xt_lookupset iStatus on iStatus.xt_lookupset_id = i.insurance_status
inner join 
    xt_lookupset iType on iType.xt_lookupset_id = i.insurance_type
inner join 
    party iParty on iParty.party_id = i.insurance_party_id
inner join 
    asset_class_vehicle v on v.asset_hdr_id = ah.asset_hdr_id
                          and v.vin_no = 'TTXJG330ML5P34985'

解决方法 #

• 先排查脏数据：insurance_cancellation_dt大概率是nvarchar类型，字段里存在非日期格式的字符串，导致SQL Server隐式转换时失败。先找出这些异常记录：

  SELECT insurance_cancellation_dt 
  FROM asset_hdr_insurance 
  WHERE ISDATE(insurance_cancellation_dt) = 0
  

• 临时修复查询：先过滤掉非日期格式的记录，再显式转换后做比较：

  inner join 
      asset_hdr_insurance i on i.asset_hdr_id = ah.asset_hdr_id
      and ISDATE(i.insurance_cancellation_dt) = 1
      and CONVERT(date, i.insurance_cancellation_dt) > '2025-01-01'
      and CONVERT(date, i.insurance_cancellation_dt) < '2025-04-01'
  

• 长期根治：如果业务允许，把insurance_cancellation_dt字段类型修改为date或datetime，彻底避免隐式转换带来的问题。

内容的提问来源于stack exchange，提问作者teenup