If you got "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error that mean that there are some values in table you select from, cannot be converted into DATETIME.

Now, you may say that this values are supposed to be filtered out by WHERE clause.

Yep, however I have seen casess where validation, conversion and division by zero erros are raised due to the SQL optimiser desides to perform relevant actions before applying WHERE filters and/or JOIN's.

Last time I've seen this behaviour it was SQL 2005 and we had very (very) large tables...

Microsoft SQL Server consultant confirmed that it is possible after speaking to MS devs working on SQL Server...

However, you may find that something more trivial causes your problem...