Compare Data Oracle

Question

I have the following column in oracle

DATA_FILTRO
01/04/2017 10:27:15

I would like to compare the month and year:

SELECT DATA_FILTRO
 FROM TABELA
WHERE TO_CHAR(DATA_FILTRO,'MM/YYYY') ='04/2017'

In PLSQL works, but in PHP gives error

oci_execute(): ORA-01843: not a valid month in

PHP code

public function metodo($data){
    $conn = new ConnectionFactory();
    $con = $conn->getConnection();
    $query = "SELECT DATA_FILTRO
                FROM TABELA
               WHERE TO_CHAR(DATA_FILTRO,'MM/YYYY') ='04/2017'";
    $stmt = ociparse($con, $query);

    oci_execute($stmt);
     while ($row = oci_fetch_array($stmt, OCI_ASSOC)){
        echo $row['DATA_FILTRO'];
     }
    $conn->closeConnection($con);
    return $protocoloList;
  }catch ( PDOException $ex ){  echo "Erro: ".$ex->getMessage(); }
}

In PHP Only works if it’s the month 05/2017

Answer 1

ORACLE does not recommend you convert a field to perform a query, because if the data_filter column has an index, when converting the column the database will not use the execution plan as the index, encumbering the execution time of your query. Another point is that ORACLE recommends using variable bind to perform dynamic queries, because when mounting a string with the date value (without bind) ORACLE will not use the statistics and will create a new execution plan, also burdening your query.

Untitled:

$query = "SELECT DATA_FILTRO
            FROM TABELA
           WHERE DATA_FILTRO = TO_DATE('01/04/2017', 'DD/MM/YYYY')";

With Bind:

$query = "SELECT DATA_FILTRO
                FROM TABELA
               WHERE DATA_FILTRO = to_date(':data01', 'dd/mm/yyyy'";
$valor_data = '01/04/2017';
oci_bind_by_name($stid, ":data01", $valor_data);

http://php.net/manual/en/function.oci-bind-by-name.php