Change Varchar field to Date Mysql

Question

I need to fix a table that was built wrong, the field that would store the date was created as varchar.

With this structure I can not select a certain period, because varchar are ordered from left to right in alphanumeric order. So selecting 30/05/2016 to 10/062016 will bring an empty result.

The question is on how to change the type of this field to date, because when I try to change the error of incorrect value, by the fact of the data that are already in the table.

How to do without losing the information that is contained in the table?

Answer 1

I think I’d better use the right kind, but the problem isn’t even VARCHAR is the content has been saved with the least relevant data before the most relevant. If it had been placed year, month and day, it would not have this problem. Databases are for storing raw data, not for storing cute text to display, this is the function of the application.

To do this you will need to take steps. First create a new temporary column of type DATE in the database. Then run a UPDATE to populate it with the dates. Then delete the old column and rename the new one with the old one (optionally).

UPDATE tabela SET datatmp = STR_TO_DATE(data_venda, '%d/%m/%Y')

I put in the Github for future reference.

Of course, if the application expects the old one to still exist, it will have to leave it. But there is a problem, the new (or updated) data will only move in the old column. The right thing is to adapt all the applications that access it to work with the new column.

Answer 2

You need to convert dd/mm/yyyy date to correct yyy/mm/dd ?

If it is solved using this script below straight in the bank .

UPDATE tbl_data SET data =
    DATE_FORMAT(STR_TO_DATE(data, '%d/%m/%Y'), '%Y-%m-%d') 
WHERE data LIKE '__/__/____'

I created an event and ran the code above. See if it helps you. After executing the code and updating the dates change the field type directly in the database, from VARCHAR to DATE