SQL Server - Convert VarChar to Date in ALTER TABLE

Question

Saw a few threads on this but I have a specific instance where I am trying to do the conversion within an ALTER TABLE statement.

ALTER TABLE Leads
ALTER COLUMN [Created Date] Date

This is throwing an error:

Msg 241, Level 16, State 1, Line 34
Conversion failed when converting date and/or time from character string.
The statement has been terminated.

Created Date is currently set as (varchar(max), null)

Answer 1

You could standardize the date format. Something like this:

UPDATE Leads
    SET [Created Date] = TRY_CONVERT(Date, [Created Date], 101);

The third argument is for the MM/DD/YYYY format mentioned in a comment. This will convert the value to a date -- if it can -- and to NULL otherwise. Then, SQL Server will use its default formats to convert back to a string.

NOTE: If you do this, be sure you back up the table, so you don't lost the information in the column!

Then, your code should work:

ALTER TABLE Leads ALTER COLUMN [Created Date] Date;

You can find the rogue values by using:

select [Created Date]
from Leads
where try_convert(date, [Created Date], 101) is null and
      [Created Date] is not null;