SQL Server - Convert VarChar to Date in ALTER TABL

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;

Answer 2

i think you must to create temp table and then insert all of records into temp table then rename temp table and original table

Answer 3

As per Jarlh:

ALTER TABLE Leads
ADD COLUMN CreatedDate Date;

UPDATE Leads
SET CreatedDate = cast(right([Created Date],4) + '-' + left([Created Date],2) + '-' + left(right([Created Date],7),2) as Date);

Alter TABLE Leads
DROP COLUMN [Created Date];

sp_rename 'Leads.CreatedDate', '[Created Date]', 'COLUMN';