PHP with PDO returns full SQL date

Question

I have a PHP application that reads data from tables that can be SQL or Postgres.

When I started using PDO instead of mssql, the SQL data fields return ex: 'Nov 29 2019 12:00:00:AM'. If you query directly on the server I get '2019-11-29' or if the call goes to postgres the result is the same.

The SQL field is date type.

// Ligar a base de dados com PDO
class Conexao
{
    private static $connection;

    private function __construct(){}

    public static function getConnection() {

        $pdoConfig  = $_SESSION["bd_tipo"] . ":". "host=" . $_SESSION["bd_host"] . ";";
        $pdoConfig .= "dbname=".$_SESSION["bd_nome"].";";

        try {
            if(!isset($connection)){
                $connection =  new PDO($pdoConfig, $_SESSION["bd_user"], $_SESSION["bd_senha"]);
                $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }
            return $connection;
        } catch (PDOException $e) {
            $mensagem = "Drivers disponiveis: " . implode(",", PDO::getAvailableDrivers());
            $mensagem .= "\nErro: " . $e->getMessage();
            throw new Exception($mensagem);
        }
    }

And to call:

$SQL = "SELECT TOP 30 * FROM assistencias WHERE id_tec=$id_tec order by 'data' DESC, 'id_assis' DESC";

try{
    $Conexao    = Conexao::getConnection();
    $query      = $Conexao->query($SQL);
    $result   = $query->fetchAll();

} catch(Exception $e){
          echo $e->getMessage();
          exit;
}

I need the date to be in format '2019-11-29'. I’ve tried the date() unsuccessful. I am using PHP 5.6.

Answer 1

Use the function:

date("d-m-Y", strtotime($originalDate);

Output:

02-12-2019

To replace "-" for "/" utilize:

$date = "02-12-2019";
$date = str_replace("-", "/", $date);

Reference strtotime

Reference str_replace