The data field is of type varchar , in June the date format is DD/MM/YYYY , but wanted to change it to YYYY-MM-DD since it is the format that MySQL accepts and to follow the current standard of July.
After I switch to the correct format, I would like to change the column data to type DATE . What kind of treatment should I do?
Based on the fact that you want to update the database directly, it follows an update where using mysql's SUBSTR function on all records that do not contain "/".
UPDATE tabela SET campo_data = CONCAT(SUBSTR(campo_data,7,4),'-',SUBSTR(campo_data,4,2),'-',SUBSTR(campo_data,1,2))
WHERE campo_data LIKE '%/%'
For example:
Date field = 06/13/2015
SUBSTR (field_data, 7.4) = 2015 // 4 positions from 7 character
SUBSTR (field_data, 4.2) = 06 // 2 positions from 4 character
SUBSTR (field_data, 1,2) = 13 // 2 positions from 1 character
Then you just concatenated in the desired format (YEAR-MONTH-DAY):
CONCAT (SUBSTR (field_data, 7,4), '-', SUBSTR (field_data, 4,2), '-', SUBSTR (field_data, 1,2))
If you are receiving the date from mysql and want to convert to the Brazilian format use the following command:
$data = implode("/",array_reverse(explode("-",$data)));
This will create the mysql date in Brazilian format.
If you want to prepare the date in Brazilian format to insert into mysql use:
$data = implode("-",array_reverse(explode("/",$data)));
@PartAugusto gave a good answer, but what he said can also be done in an object-oriented way. It is a solution that is already PHP itself, through class DateTime .
$datetime = DateTime::createFromFormat('d/m/Y', $data);
$datetime->format('Y-m-d');