Problems formatting the date type returned from select

Question

Problems formatting the date type returned from select

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)
#4 by (1 votes)

1

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 year being reduced. example this and my current return [{"Mes_Ref":"2016-04-01"}] and I would like the return to be [{"Mes_Ref":"04/16"}] . or [{"Mes_Ref":"04-16"}] does. follow 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

Pleasenotethatithasbeenprintedformeforthelast12monthsoftheuserandIwouldlikeittocontinueasIsaidearlierinplaceof[{"Mes_Ref": "2016-04-01"}] and I would like the return was this [{"Mes_Ref": "04/16"}]. or [{"Mes_Ref": "04-16"}] Anyway. Obs; I used the date.formate and it generated error in return instead of displaying the last months it displayed like so:

Noticethatthevaluesarenotthesamebecausesomehowthedate.formatereturnedmeonlythemonth"12" of each year which for me does not serve Can someone help me? I thank you

php mysql date

asked by anonymous 04.05.2016 / 16:51

4 answers

2

try using the date format function of mysql

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

04.05.2016 / 17:19

1

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);

04.05.2016 / 17:11

1

You can not use the same name D.Mes_Ref with the nickname:

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

Change the nickname to any other

20.05.2016 / 21:52

Check if SQL Server is installed C # Limiting texts by putting ellipses at the end, using angularjs

score 3 · Accepted Answer

I was able to clean up as I wanted to do as follows

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

This way of doing gets the D.Mes_Ref example (05/19/2016) from the bank and modifies it to (05/2016) or just gets mes and year. thank you guys