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)
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;
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';
i think you must to create temp table and then insert all of records into temp table then rename temp table and original table
