Convert date format DD/MM/YYYY to YYYY-MM-DD stored in a scan

Question

The field data is the type of varchar, in the month of 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 pattern of July. After I switch to the correct format, I would like to change the column data for the guy DATE . What kind of treatment should I do?

Answer 1

Based on the fact that you want to update the database directly, follow an update where using the mysql 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 = 13/06/2015

SUBSTR(field date,7,4) = 2015 //4 positions from 7 character

SUBSTR(field date,4,2) = 06 //2 positions from 4 character

SUBSTR(field date,1,2) = 13 //2 positions from 1 character

Then just concatenate into the desired format (YEAR-MONTH-DAY):
CONCAT(SUBSTR(campo_data,7,4),'-',SUBSTR(campo_data,4,2),'-',SUBSTR(campo_data,1,2))

Answer 2

If you are receiving the mysql date and want to convert to 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 in mysql use:

$data = implode("-",array_reverse(explode("/",$data)));

Answer 3

The @pedroAugust 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 the class DateTime.

$datetime = DateTime::createFromFormat('d/m/Y', $data);

$datetime->format('Y-m-d');