Use TO_DATE in SQL Server 2012

SQL-Server has no TO_DATE function. You have to use convert. See here

-- Specify a datetime string and its exact format
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;


-- Specify a datetime string and style 102 (ANSI format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '2012-06-05', 102);

  -- TRY_CONVERT available since SQL Server 2012 (returns NULL if conversion fails)
  SELECT TRY_CONVERT(DATETIME, '2012-06-05', 102);

For your specific case use:

convert(DATETIME, '2011-11-09 00:00:00')

TO_DATE() isn't a valid function in SQL Server (T-SQL)

The alternatives depend on what version of SQL Server you are using

CAST() or CONVERT() may be used in any version. Starting with SQL Server 2012 onward one can use TRY_CAST() or TRY_CONVERT(). The advantage of using the latter pair is they do not error if the strings cannot be converted when they return null instead.

For date/time strings generally using CONVERT() or TRY_CONVERT() is used as you can pass "style" numbers where YYYY-MM-DD is "style" 102 e.g.

SELECT TRY_CONVERT(DATE,'2017-01-21',102)

However it is possible to use CAST/TRY_CAST like this example:

SET DATEFORMAT mdy;  
SELECT TRY_CAST('12/31/2016' AS datetime)