The bazaar thing is that I've tried looking at the sub-queries, I've looked at every field referenced in every table, including looking at each and every implicit conversion. I would try to replicate the issue for you but suspect that it is some incredibly strange sequence of events that could not be replicated if I tried.

I suspect that the issue is somewhere SQL server is behaving in a way that it shouldn't, there is a conversion that uses a case when statement to exclude the values that would cause an error, if SQL server tried to convert this inside the case when then I would see this conversion error.

The TOP XXX rows shouldn't be relevant because the result set in the data used is under 1000 rows, if I set it at 1000 or more I get every single row without the error occurring.

Whilst I don't claim to be flawless in anyway, I'm almost certain that the issue stems from SQL Server processing something in the wrong order. When I say wrong order please don't assume me ignorant to the order that calculations are normally performed. I believe it must be either evaluating the JOIN criteria of one of the nested queries before evaluating the nested query properly, or evaluating the conversion of a field and applying the case when inside the conversion afterward.

For Example there is code that does this:

CONVERT(DATE, CASE WHEN [date_field_as_text] = '00000000' THEN NULL ELSE [date_field_as_text] END,112) as [DATE]

this occurs inside a nested query and is used to join to another table where the other table is correctly stored as a date. The join criteria is a combination of this date field and another string field where the string fields are certainly not date data types implicitly or otherwise. Further the date fields are correctly converted to dates

E.g.

ON a.ID = b.ID

And a.Date = b.Date

I can provide more information a bit later.

Sorry for not being more helpful.