Why is casting a string to DATETIME not accurate to the ms?
DateTime has a precision of has a  0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th.

If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value.

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));