The whole issue revolves around CASTing and quirky PostgreSQL syntax.

CASTing strings as DATEs:

It's very simple - your first line of code:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07');
ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: SELECT EXTRACT(DOW FROM '2011-07-07');
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
ntest=# 

does not work. This is because, as far as PostreSQL is concerned, '2011-07-07' is just a string literal, not a DATE. This does work:

ntest=# SELECT EXTRACT(DOW FROM DATE '2011-07-07');
 date_part 
-----------
         4
(1 row)

Note the extra DATE that CASTs the string as such. You can also CAST it this way:

ntest=# SELECT EXTRACT(DOW FROM '2011-07-07'::DATE);
 date_part 
-----------
         4

This double colon (::) is very much a PostreSQL thing. It's really quite "obvious", once you grasp the PostgreSQL syntax (it had me stumped for a couple of minutes!)

A date field:

ntest=# \d orders;
       Table "public.orders"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 ldate       | date    | 
 customer_id | integer | 
 amount      | integer | 

So, we have a date field - with records as below:

ntest=# SELECT * FROM orders;
   ldate    | customer_id | amount 
------------+-------------+--------
 2009-10-08 |           3 |   3000
 2009-10-08 |           3 |   1500
 2009-11-20 |           2 |   1560
 2008-05-20 |           4 |   2060
(4 rows)

So, to get the "day of week" from a date (note the two FROMs - doesn't work without the FROM between brackets):

ntest=# SELECT EXTRACT(DOW FROM ldate) FROM orders;
 date_part 
-----------
         4
         4
         5
         2
(4 rows)

Now, for a timestamp field:

ntest=# \d timestamptest;
           Table "public.timestamptest"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 x      | timestamp without time zone |

And:

ntest=# SELECT * FROM timestamptest;
             x              
----------------------------
 2016-04-18 22:52:30.020979
(1 row)

And DOW (again, note the two FROMs):

ntest=# SELECT EXTRACT(DOW FROM x) FROM timestamptest;
 date_part 
-----------
         1

Quirky syntax (the double colon also OK, if you want to be really explicit), but it works fine once you've mastered it.

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]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < convert(varchar(23, getdate(), 121)

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