I have been using a query that joins several raw tables (nvarchar datatypes), has several conversions, nested queries, CASE WHEN statements. In other words it is messy, but I didn't create it nor do I have time to recreate it properly. It has been generating a very strange conversion error (the cause not the error itself is strange) where there is some kind of datetime conversion error when converting a string datatype. I have debugged every single field from every table in the query that is either converted explicitly or implicitly (these have all been updated to explicit conversions), with no problems present.
The error is very strange because if I change the select fields (none of which are converting to a datetime implicitly or otherwise) to * the error message goes away, in fact if I simply add ,* after the last field in the select list it also goes away. However I can't use this because don't want every single field from every table in the query.
Further, if I change the Select statement to SELECT TOP XXX where XXX is greater than the number of rows returned the error still does not occur. But if I use SELECT TOP 100 PERCENT the error occurs again.
This made me think that it might be an error in the query execution cache. Which I cleared and tried again with the same errors still occurring.
The database is set up to use Korean_Wansung_CI_AS on SQL Server Enterprise Edition v10.50.1600.1
This problem is not critical as I can set it to use SELECT TOP XXX such that XXX is far larger than will ever be actually required. It was just very strange behavior that I had never seen before.
Unfortunately, I can't provide the code or data both are quite confidential, however I'm quite sure that this is not directly related to the code itself, as the code is working using the two scenarios mentioned.
Has anyone seen this behavior and/or know of the cause?