Date comparsion within subquery fails with: "out-of-range value"
The execution order is in much regards up to the query optimizer. So in your case, even thou the SQL code you wrote looks like the subquery would filter out the invalid dates first, what the actual execution of the query looks like, can be very different.

You can run your query with option(force order), but be careful with that one, very careful, and know what you are actually doing. Another option would be a temp table, to preselect the valid or the invalid dates, but that could be very costly as well. 

While it feels dirty, and it is, you could also compare string values, to not run into this issue, just make sure to use a codepage that will sort as a string as it would as a date

select a.[id] 
FROM (
    select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
    FROM [dbo].[input]
    where len([START_DATE]) &gt; 0     
    and substring([START_DATE],7,5) &gt; 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd &lt; convert(varchar(23, getdate(), 121)


all in all, it would be best to save the start_date as an actual date type thou.