How to identify errors from casting a column?

Question

I have a table that is all NVARCHAR. I am trying to cast a column into a date; however, there appears to be some value in the column that will not cast as date and is throwing an error halfway through.

I am trying to find the rows that are problematic. However, I don't know how to equal a result to an error..

SELECT id, 
CASE WHEN CAST(date_of_birth AS DATE) = error
     THEN 1
     ELSE 0
FROM TABLE1

Table1

id    date_of_birth
1     05/05/1934
2     feathers
3     06/06/1843

Expected results

id    date_of_birth
1     0
2     1
3     0

Any advice on how to isolate the problematic rows?

Answer 1

Use TRY_CAST():

SELECT id, 
       (CASE WHEN TRY_CAST(date_of_birth AS DATE) IS NULL AND date_of_birth IS NOT NULL
             THEN 1
             ELSE 0
        END) as is_bad_date_of_birth
FROM TABLE1;

Answer 2

To identify error rows, use the ISDATE function:

SELECT * FROM TABLE WHERE ISDATE(date_of_birth)=0