Ralf (7/19/2013)
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
Ralf
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:
SELECT B.Date
INTO #t
FROM A
INNER JOIN B
ON A.ID = B.ID
WHERE A.ID <> 0
SELECT CONVERT(DATETIME,Date)
FROM #t