varchar to datetime mysql


i have huge db in which unknowingly i kept the column as varchar which actually has to be datetime. The data stored in it is in '16/September/2011' format.
Now i want to convert the data type to datetime without losing the data.
ie 2011-09-11 rest part can be ignored
is it possible?

db : mysql
programing language : php
LVL 15
Who is Participating?
johanntagleConnect With a Mentor Commented:
To complete the procedure:

1.  create a new date column
2.  run: update table_name set date_column= STR_TO_DATE(original_column,'%d/%M/%Y');
3.  drop the old column

in your case it should be STR_TO_DATE('16/September/2011','%d/%M/%Y')

Hugh McCurdyCommented:
Here's what I'd do.

1. Insert a new column.
2. Modify any software you have that uses the old field and change it to use the new field.  Hopefully you'll find everything.
3. Write a PHP program to read from the old column and write to the new column.  
3a. If the new column has data, I wouldn't overwrite it unless you are interested in the most recent date in which case, I'd overwrite it if not the most recent.  Why?  In case you don't find all instances that use the old field in step #2.

4. If you are absolutely certain that you don't have any software remaining that uses the old column (and you are absolutely certain you'll never need to revert to a prior version) then you can delete the old column.  Me, I'd keep the old column around for quite some time.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Agree with hmccurdy's steps, except you don't need a PHP program for step 3.  To not overwrite on existing data, just do:

update table_name set date_column= STR_TO_DATE(original_column,'%d/%M/%Y') where data_column IS NULL;
data_column --> date_column
Hugh McCurdyCommented:
johan, you are correct.  I just like writing PHP programs.  
InsoftserviceAuthor Commented:

i had that code but with different format and thats the reason why it was not working i did not dropped so as to be in safe hand.
Thanx it worked
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.