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