Why date comparison is not working in this SQL query?

I am getting this error:

Error converting data type nvarchar to bigint

when I try to run this query below. If I remove the date comparison condition, it works fine.

 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'

This sounds like a schema issue, where the insurance_cancellation_dt is incorrectly defined as a big int.

With that in mind, the best solution is to fix the data (because what you have really would be considered broken). But I understand this isn't always possible in an existing system.

With that in mind, I can't be sure of the exact solution without seeing a sample of what the actual data looks like but I expect the commented lines should probably look more like this:

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

Note the change in operator for the first inequality expression. The original operator looked wrong, and should probably use >= instead of > (but the < rather than <= is probably correct).

---

Additionally, I see this:

and c.reference='100981'

I don't think this is the cause of your error, and normally I'd find it unremarkable, but since we potentially have other type-mismatch situations in the query, there could be something here worth addressing. If c.reference is actually a numeric type of some kind, rather than a string/varchar type, the SQL Server type precedence rules may require converting ALL the c.reference values to varchar, rather than converting '100981' to the numeric type once. It would need to do this for every row, even values that won't match the expression... because how else would it know whether or not the value matched? Worse, this breaks the ability to use any index you might have for this column.

---

Now we have this new info:

insurance_cancellation_dt is a datetime ... It also works if I remove i.asset_hdr_id=ah.asset_hdr_id and keep the date comparison

It's odd removing conditional expressions fixes the issue (hence: a Stack Overflow post 😉). If there were a type conversion problem elsewhere, more conditional expressions would tend to reduce the size of the result set, which might help avoid the error, where removing an expression would tend to add rows and therefore increase the chance of error. This does the opposite.

Still: the query optimizer can do strange things. For example, adding an expression might lead to walking a narrow index vs seeking into a broader table, or vice versa. In this way, adding the expression could mean you look at more rows but have fewer page reads. There are other reasons this could have odd results, as well.

What is certain is somewhere this is causing a conversion from one data type (nvarchar) to the another (bigint) you did not intend. Paying better attention to column and literal data types will allow you to avoid this.

It's further worth noting there are no nvarchar or bigint literals anywhere in the query. nvarchar would need an N prefix, and there's nothing numeric large enough for bigint. There are also no conversion operations in the SELECT clause. Therefore this MUST be a conversion happening within a conditional expression between two columns in an ON or WHERE clause. I'd pay closest attention to these three expressions:

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

Where it's possible one of the insurance_status, insurance_type, or acf.reference columns mostly holds strings representing ID values into the paired tables, but are defined as nvarchar to also allow for a wider range of status/descriptive information in some rows. Remember: strings representing ID values are not the same as holding the ID values directly.