CODEWITHSUNDEEP
mysqlsql
TheMightyLlama
TheMightyLlama

Reputation: 1273

How can I change a MySQL column from VARCHAR to date time and convert the data at the same time?

I have a large set of data with a varchar column which contains an unusual date time format.

How can I both convert the data in the 6000+ rows, and then convert the column's type?

I can see that it's possible to convert the type with this:

ALTER TABLE <tblName> MODIFY <columnName> date time;

But I don't see how I can keep the data and do this for all rows at the same time.

An example date that I currently have is: 

Mon, 23 Sep 2013 07:01:00 GMT

Answer as per @Mihai

UPDATE rns
SET rns.`rns_pub_date` = STR_TO_DATE(rns_pub_date,"%a, %d %b %Y")

Upvotes: 0

Views: 4422

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

You don't do it all at once. You take these steps, one by one.

  1. Add the new column.
  2. Update the new column from the old column. This may take more than one query depending on how many formats you have in your varchar column.
  3. Drop your old column.
  4. Rename your new column.

Upvotes: 4

Mihai
Mihai

Reputation: 26784

UPDATE `table`
SET `column` = STR_TO_DATE(`column`,'%Y-%m-%d')

Adapt the format to your needs.

Upvotes: 1

Related Questions