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.