Hi,
You can use "IsDate" function of SQL to check whether the string is in proper date format or not.
Can you please try with below query ?
SELECT MAX(T.DATE)
FROM (
SELECT CASE
WHEN ISDATE(YourDate) = 1
THEN CAST(YourDate AS DATE)
ELSE NULL
END AS DATE
FROM YourTable
) T
Please let me know if you have any concern or query on this.
Thanks
Advay Pandya