What you can do is update the value using try_convert() first and then alter the column name. Note: This will set any invalid values to NULL .

update customer
    set dob = try_convert(date, dob);

alter table customer alter column dbo date;

If you want to see the bad values, then before you change the table, run:

select c.*
from customer c
where try_convert(date, dob) is null and dob is not null;

You may have other ideas on how to fix the values.

You can't change from varchar to date or time or datetime by altering the column. Why? Because SQL Server does not know if the field contains '1/1/2020' or 'My dog is cute'. You will have to rebuild the table with the proper data types and then CAST() or CONVERT() the values to a true date time.

Underneath the hood, this makes more sense. A char/varchar uses one byte per character. nchar/nvarchar uses 2 bytes per character. A datetime is a number, not a character. This means you need a routine that changes this into the correct number. If you want to get deeper, the number is the number of ticks (nanoseconds) since midnight on January 1, 0001 in the Gregorian Calendar. More info .

You can use the ISNULL function in SQL to convert an empty string to a null DateTime value The NULLIF function is used to return NULL if the value in date_column is an empty string. The ISNULL function then takes the result of NULLIF and replaces it with NULL if it is still NULL.

UPDATE table_name
SET date_column = ISNULL(NULLIF(date_column, ''), NULL);