Check if value is date and convert it
use isdate function like ..
(CASE WHEN ISDATE (invoice_date) = 1
THEN convert(datetime, cast([invoice_date] as char(8)))
END) AS Invoice_Date
You can use the ISDATE function, but what would do for non-date values? In my suggested solution you can choose to return a null:
select
(case
when ISDATE (invoice_date)=1
then convert(datetime, invoice_date)
else null end) AS Invoice_Date
from your_table
ISDATE
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0. ISDATE returns 0 if the expression is a datetime2 value.
Please visit