Give isdate a try. Here is the test I tried:

CREATE TABLE Foo (Id int not null identity(1,1), stringdate varchar(30))

INSERT INTO Foo VALUES ('1/1/2014'),('abc'),('01012014'),('1114'),('20140101')

SELECT stringdate, cast(stringdate as datetime)
FROM (SELECT stringdate FROM Foo WHERE ISDATE(stringdate) = 1 ) x

This only returned entries for '1/1/2014' and '20140101' because they were the only ones I could validly convert to a date. Note: The behavior of isdate will depend on certain settings on your server. SET LANGUAGE for example but should handle the problems you are having.

Link to BOL for isdate: http://msdn.microsoft.com/en-us/library/ms187347(v=sql.100).aspx

Give isdate a try. Here is the test I tried:

CREATE TABLE Foo (Id int not null identity(1,1), stringdate varchar(30))

INSERT INTO Foo VALUES ('1/1/2014'),('abc'),('01012014'),('1114'),('20140101')

SELECT stringdate, CASE WHEN ISDATE(stringdate) = 1 THEN cast(stringdate as datetime) END
FROM Foo  
WHERE ISDATE(stringdate) = 1 

This only returned entries for '1/1/2014' and '20140101' because they were the only ones I could validly convert to a date. Note: The behavior of isdate will depend on certain settings on your server. SET LANGUAGE for example but should handle the problems you are having.

Link to BOL for isdate: http://msdn.microsoft.com/en-us/library/ms187347(v=sql.100).aspx