Converting Date (d-m-Y_His) stored in mysql VARCHAR to mysql DATETIME

EICT
EICT used Ask the Experts™
on
Hi,
I was trying to sort records in one of our MySQL databases by date but I notice that the date values are stored as VARCHAR data type rather than a DATETIME value.

i.e.  08-07-2016_164533     in the format d-m-Y_His  (day-month-Year_HourMinuteSecond)

I wonder if there is an easy way to convert these values to DATETIME datatype for easier manipulation?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Top Expert 2013
Commented:
There is not an easy way to do it with a single command. It is a multiple step process.
1. Create a new column in your table:
ALTER TABLE tab ADD COLUMN col2 DATETIME;

2. Update the new column from the old one:
UPDATE tab SET col2 = STR_TO_DATE(col1, '%d-%m-%Y_%k%i%S');

3. Validate
SELECT col1, col2 FROM tab;

4. Drop the old column:
ALTER TABLE tab DROP COLUMN col1;
skullnobrains
Commented:
agreed except i'd use

1. lock table
2. create table table_tmp as select .... , STR_TO_DATE(col1, '%d-%m-%Y_%k%i%S') from table
3. rename table to table_old , table_tmp to table
4. drop table table_old

which will copy the whole table a single time rather than twice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial