Problems formatting the date type returned from select

Question

Good morning I would like you to help me with a small problem that I am trying to return from a select in php. I would like the return of my "Mes_ref" from my select to be only month and year without the day and the year to be reduced. example this and my current return [{"Mes_Ref":"2016-04-01"}] and I wish the return was this [{"Mes_Ref":"04/16"}]. or [{"Mes_Ref":"04-16"}] whatever. follows my code below

<?php
//-------------------------------------------Demanda--------------------------------------------------///////

mysql_connect('blablabla','uaaa','lll');
mysql_select_db('aaaa') or die (mysql_error());



$Cod_Empresa = $_GET['aaaa'];
$aa = $_GET['aaa'];
$aa = $_GET['aaa'];
$aa = $_GET['aaaa'];
$tensao  = strtoupper($_GET['aa']);

if (($di != "") && ($df != ""))
    {
        $periodo = '
        && D.Mes_Ref >= "'.$aa.'"
        && D.Mes_Ref <= "'.$aa.'"
            Order By Mes_Ref DESC
                ';
    }
else
    {
        $periodo = '
            Order By Mes_Ref DESC
            Limit 0,12
                ';
    }
switch($tensao)
    {
        case 'BT':
            $tensao_ = "";
            break;
        case 'AT':
            $tensao_ = "
                && D.Tip_Fatur = 0
                && D.Classe in ('A1','A2','A3','A3a')
            ";
            break;
        case 'MT':
            $tensao_ = "
                && D.Tip_Fatur = 0
                && D.Classe in ('A4','As')
            ";
            break;
        case 'ML':
            $tensao_ = "
                && D.Tip_Fatur = 1
            ";
            break;
    }   

switch($tensao)
{
    case 'BT':
            $sql = "Select Mes_Ref,
                        round(KW_P_Reg,0) as KWP ,
                        round (KW_FP_Ind_Reg,0) as KWPFP 
                From Tab_Fatura_BT D
                WHERE Cod_Empresa = ".$Cod_Empresa."
                && Cod_UC = ".$aa."
                ".$tensao_."
                ".$periodo."
                    ";
            break;                      
        default:
                $sql = "SELECT D.Mes_Ref,
                            round(D.Contr_KW_P,0) as Contr_KW_P , 
                            round(D.Contr_KW_FP,0) as Contr_KW_FP,
                            round(L.KW_P_res + L.KW_P_per,0)as KWP , 
                            round((L.KW_FP_res + L.KW_FP_per),0)as KWPFP 
                    FROM Tab_Fatura_Dados D, Tab_Fatura_Leituras L
                WHERE D.Cod_Empresa = ".$Cod_Empresa."
                && D.Cod_UC = ".$aa."
                && L.Cod_Empresa = D.Cod_Empresa
                && L.Cod_UC = D.Cod_UC
                && L.Cod_Fatura = D.Cod_Fatura
                ".$tensao_."
                ".$periodo."
            ";
            break;
}


    $query = mysql_query( $sql ) or die('Could not query');                             


    for($rows = array(); $row = mysql_fetch_object($query); $rows[] = $row);
            {       
                echo json_encode($rows);    
            } 

?>

the figure below shows the return of select

See that he printed for me the last 12 months of the user and I would like it to continue as I said at the beginning in place of [{"Mes_ref":"2016-04-01"}] and I would like the return to be this [{"Mes_ref":"04/16"}]. or [{"Mes_ref":"04-16"}] whatever.
Note; I used the date.formate and it generated error in the return instead of showing the last months it displayed so:

notice that the values are not equal pq somehow the date.formate returned me only month "12" of each year which for me does not serve Somebody might be able to help me. thanks

Answer 1

staff managed to arrange as I wanted just do as follows

$sql = "SELECT REPLACE(date_format(D.Mes_ref,'%m-%y') , '-', '/' ) as Date,

This way of doing receives the example D.Mes_ref (19/05/2016) from the bank and modifies to (05/2016) or just stays with month and year. thank you guys

Answer 2

try using the mysql date format function

select date_format(D.Mes_Ref,'%m/%Y')

Answer 3

You can’t use the same name D.Mes_Ref with the surname :

$sql = "SELECT REPLACE(date_format(D.Mes_Ref,'%m-%y') , '-', '/' ) Mes_Ref,

Change the nickname to any other

Answer 4

You can use the date function to store the formatted date before returning json_encode.

Ex:

$retorno = [];

while ($row = mysql_fetch_assoc($query));
      {       
          $row['Mes_Ref'] = date('m/Y',strtotime($row['Mes_Ref']));
          $retorno = $row;

      }

 return json_encode($retorno);