For the last 3 years I work with huge amount of data and also performs data cleansing. I wrote an article based of my experience which is: String Manipulation on large data using LIKE operator or PATINDEX() in MS SQL.
In this article I provided lots of examples of how to convert different date formats into a unified date and provided the idea when to use the PATINDEX() instead of the LIKE operator.
