CODEWITHSUNDEEP
mysqlsqldatedatetime
João Correia
João Correia

Reputation: 105

MySQL alter table and convert data from text to datetime

I have a table with a column date but it is stored as text.

Now I need to extract data based on date and I'm thinking I need to alter the column type to datetime, but how can I do that without losing data? My text records are in format dd-MM-YYYY hh:mm

If I just change the column type the data I lose all data (it is filled with zeros).

Upvotes: 8

Views: 10069

Answers (4)

Greenleaf
Greenleaf

Reputation: 535

I agree, @CodeGodi. This answer is slightly different and has a bit of explanation.

My old column was text that looked like this: '1/1/2010'.

Working in 2018 with MySQL MariaDB latest versions and after trying many people's suggestions to no avail, I did this:

alter table mytable add column new_date DATE;
update mytable
set new_date = str_to_date(`date`, '%m/%d/%Y')

Specifically, this means "Alter the table 'mytable' to add a column called 'new_date' that has the date format.

"This will, of course, be an empty column. To populate it, update 'mytable' again to populate the 'new_date' column with the string values currently stored in the old text date column. But convert those string values: Extract the month from before the first slash, the day from between the two slashes, and the year, in four digits, by taking what is after the last slash."

Upvotes: 2

CodeGodie
CodeGodie

Reputation: 12132

You guys both had the right answer but I had to tweak it some more to get it working for me.

I originally had column date set with a TEXT format with this line of text:

Tue Aug 20 9:52:13 EDT 2013

What I did to get this going is the following:

1) Created a new column called date_transformed with the DATETIME format

2) Run the following code:

UPDATE `table`
SET `date_transformed` = STR_TO_DATE(`date`,'%a %b %d %H:%i:%S EDT %Y')

When you do this, MySQL will copy the date from one column to the other, transforming into a date formatted column. You can then rename the column as you wish and delete the old column you no longer need. I hope this helps.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

UPDATE `table`
SET `column` = STR_TO_DATE(`column`,'%d-%M-%Y %h:%i')

Just change the format to what you have,in case that is not correct.

Formats

SQL fiddle

Upvotes: 5

Jorge Campos
Jorge Campos

Reputation: 23381

You can create a new column of type DATETIME then you update it with your converted data from your TEXT column using the datetime conversion functions from mysql.

After update this field youe can change your old field with the data from the new field then drop the field created for store the temp data.

Upvotes: 3

Related Questions