What happens when you do this?
DECLARE @REAL_DATES TABLE
(
CompletionDate VARCHAR(46)
);
INSERT INTO
@REAL_DATES
SELECT CompletionDate
FROM dbo.Entity;
SELECT
CAST(RD.CompletionDate AS datetime) AS casts_fine
FROM @REAL_DATES RD;
What I was getting at on twitter was that the conversion can be attempted by the optimizer before rows are eliminated, so you can't only consider the CompletionDate values that are returned by the join.
My first suggestion would be to use the right data type. Why are you using VARCHAR(46) to store a date? This is why you have bad data in the table and why you have to explicitly convert when you want rich data that is not a string (and should never have been a string in the first place IMHO).
My next suggestion would be to correct ALL data in that column, and put measures in place so that it won't become invalid again. For example, a check constraint that validates ISDATE(columnname) = 1.
Failing those two, next on my list would be to return the data to the client and let it convert to datetime or for display or what have you. No matter where you filter out the rows that are causing the problem, the optimizer could push that evaluation around so that the convert is attempted before the bad rows are weeded out.
And finally, you could dump the result of the query into a temp table / table variable, and perform the convert as a second step when querying that intermediate object (since you should be confident that the dates here are valid - in fact you can check first and raise an error if your join happened to return some rows with invalid dates).
Bottom line: (a) you can't make any assumptions about where in the stack a conversion attempt will be made and (b) these workarounds and hacks would not be necessary if you used the right data type.