Convert Mysql date (YYYY/mm/dd h:m:s) to dd/mm/yyyy

Question

Check out my database:

I’m using the following code:

<?php 
$data = $noticia->data_cadastro;
setlocale(LC_ALL, "pt_BR", "pt_BR.iso-8859-1", "pt_BR.utf-8", "portuguese");
date_default_timezone_set("America/Sao_Paulo");
echo strftime("%A, %d de %B de %Y", strtotime($data));
?>

Content of the variable: $data: var_dump: "string '09/02/2015 15:55:30' (length=19)"

Returns in format: "Wednesday, 02 September 2015".

I would like you to return "Monday, February 9, 2015"; and then customize to "09 February 2015";as I do?

Differs from posting:"Format date in PHP" for where is echo strftime( '%A, %d de %B de %Y', strtotime('today')); in my case has a variable $date, simple, but it’s making a difference in my case.

Answer 1

If you want to return only 09 Fevereiro 2015, then do it this way:

setlocale(LC_ALL, "pt_BR", "pt_BR.iso-8859-1", "pt_BR.utf-8", "portuguese");

echo strftime("%d %B %Y", strtotime($data));

In his example, there seems to be an error of interpretation between mm and dd, and vice versa.

In this case, this solution below would work, but I still recommend that you identify if there is any date formatting error caused by regionalization of the same.

$data = DateTime::createFromFormat('m/d/yy', $noticia->data_cadastro)->getTimestamp();

echo strftime("%d %B %Y", $data);

See that we create the date from the format m/d/yy, and not as d/m/yy, because of the formatting of your date.

Updating

However, if you want to do exactly as it is in the title of the question, that is "Convert Mysql date (YYYY/mm/dd h:m:s) to dd/mm/yyyy", then you can do so.

echo DateTime::createFromFormat('yy/m/d H:i:s', $data)->format('d/m/Y');

Answer 2

This should work:

setlocale(LC_ALL, "pt_BR", "pt_BR.iso-8859-1", "pt_BR.utf-8", "portuguese");
date_default_timezone_set('America/Sao_Paulo');

$olddata = '09/02/2015 15:55:30';
$data = str_replace('/', '-', $olddata);

// Formato para por no Layout (pedido antes da edição da pergunta)
echo strftime("%m %B de %Y", strtotime($data)); // 02 fevereiro de 2015

// Como pedido após a edição da pergunta, personalizar a data para 09 de fevereiro de 2015
echo strftime("%d de %B de %Y", strtotime($data)) // 09 de fevereiro de 2015 

DEMO

Answer 3

You can do this directly in the mysql query

Using the DATE_FORMAT()

Example

SELECT *, DATE_FORMAT(data_cadastro,'%d/%m/%Y') AS data_formatada FROM sua_tabela 

Answer 4

I don’t know if it works on MYSQL, but in the PostgreSQL would be equivalent to

to_char(data_cadastro, 'DD/MM/YYYY')