How to convert a varchar column type to date type without losing the dates

Question

I am looking for a way to change the datatype of a column. Currently, in my database, the date columns types were defined as varchar and I need to convert them back to the date type.

Any idea how to do it?

Answer 1

You will need to adapt this based your your exact table structure but something like;

CREATE TABLE temp (startdate varchar(255), stuff varchar(255));

INSERT INTO temp
SELECT startdate,stuff
FROM mytable;

TRUNCATE TABLE mytable;

ALTER TABLE mytable ALTER COLUMN startdate DATETIME NOT NULL;

INSERT INTO mytable
SELECT CAST(startdate AS DATETIME), stuff FROM temp;

DROP TABLE temp;