CODEWITHSUNDEEP
mysql
Mansoor Ahmed
Mansoor Ahmed

Reputation: 83

Convert column data type from VARCHAR to DATE in MySQL?

I have a column with VARCHAR data type which have dates(values) as strings like dd/mm/yyyy

I want to change its data type to DATE and want to preserve the entered dates(value) when I try to do it, like: 

ALTER TABLE t1 MODIFY col1 DATE;

I lose the strings and the values get converted to 0000.00.00

How can I change the column data type without losing my data.

Upvotes: 2

Views: 3803

Answers (2)

Redc
Redc

Reputation: 11

When I have this problem I solve it with two temporary columns so as not to lose the main values, it is very similar to Erubiel's answer.

Table T1; Original Column "myvarchar"

1.- Insert new columns

ALTER TABLE T1 ADD Temp_ColDate1 VARCHAR;
ALTER TABLE T1 ADD Temp_ColDate2 DATE;

2.- n the first column that was added, the cut and armed values were placed according to the DATE form, but in a VARCHAT column 

UPDATE  T1  SET Temp_ColDate1 = (SELECT CONCAT((SELECT RIGHT(myvarchar, 4)),(SELECT MID(myvarchar, 4, 2)),(SELECT LEFT(myvarchar, 2))))  WHERE 1=1;

3.- Then you do a conversion of the values from VARCHAR to DATE in the temporary column 2

UPDATE  T1  SET Temp_ColDate2 = (SELECT CONVERT(Temp_ColDate1, DATE))  WHERE 1=1;

4.- Delete the temporary column 1 

ALTER TABLE T1 DROP COLUMN Temp_ColDate1;

5.- Remember that the format that you will have in Temp_ColDate1 will be from 0000-00-00 (YYYY-mm-dd), when you need a value just change the presentation format and you're done.

6.- You can delete "myvarchar" and rename "Temp_ColDate2" as "myvarchar"

With this method you can check if there is a problem on any of the dates when you decompose them. I hope it helps you. Note that this is only if the other direct methods do not work.

Upvotes: 1

Erubiel
Erubiel

Reputation: 2972

I would,

  1. Add a temporal column
ALTER TABLE t1 ADD temp_date DATE;
  1. Update this temporal column casting the value CAST('2000-01-01' AS DATE)
UPDATE t1 SET temp_date=CAST(yourvarcharcol AS DATE) WHERE 1;
  1. Update the original column type
ALTER TABLE t1 MODIFY yourvarcharcol DATE;
  1. Update the original column with the temporal column.
UPDATE t1 SET yourvarcharcol=temp_date WHERE 1;
  1. Remove the temporal column
ALTER TABLE t1 DROP COLUMN temp_date

Upvotes: 3

Related Questions