yes, it seems that the conversation to datetime is performed before the WHERE filter.
maybe because of parallel processing and the large size of the table A (~300 MB)....this is new to me
the thing is, because of the error I'm not able to check the execution plan....or is there a trick to show the execution plan anyway?
thank's a lot
Not many people believe me too. But it does happen (at least in SQL2005) and it was confiremd by MS that is't possible.
Now. How to overcome this?
We found few ways to deal with this sort of behaviour, it depends on what you are really doing.
If for example query like that: SELECT Col1/Col2 FROM Table WHERE Col2 != 0
causes the "Devision by zero" error, the easierst way to fix it would be:
SELECT Col1/NULLIF(Col2, 0) FROM Table WHERE Col2 != 0
Cases with CONVERSION or filtering via JOIN's are a bit more complecated.
So, basically you need to SELECT INTO valid records into temp table, then perform your conversion.
My understanding is that your query could potentially try to convert non-datetime value if you wouldn't have a WHERE clause or JOIN.
So, try doing that:
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID <> 0
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help