ORA-00932 inconsistent datatypes: expected DATE got NUMBER

Question

I am trying to input a date value from the user and then using that value in the query.

My date value is like this 2017.07.21 08:59:26

$MYDATE= $data[$i]->MYDATE;
//ALSO I ADDED
$str = oci_parse($conn, "ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY HH24:MI:SS'");
oci_execute($str);
//
$sql = 'INSERT INTO MYTABLE(ID,MYDATE)'.'VALUES(:ID,to_date(:MYDATE,\'YYYY.MM.DD HH24:MI:SS\'))';
$compiled = oci_parse($conn, $sql);
oci_bind_by_name($compiled, ':ID', $ID);
oci_bind_by_name($compiled, ':MYDATE', $MYDATE);
oci_execute($compiled);

It gave me this error:

inconsistent datatypes: expected DATE got NUMBER

If you describe MYTABLE, print $MYDATE, and print $ID, then chances are pretty good you'll figure it out. — Jeff Holt
Edit your post, add the output of describing MYTABLE and then show the value for $ID. — Jeff Holt
I already mention about it "My date value is like this 2017.07.21 08:59:26" — user7732643

Christopher Jones Christopher Jones · Accepted Answer · 2021-04-06T00:55:42

This script:

<?php

/*

drop table mytable purge;
create table mytable (id number, mydate date);

*/

error_reporting(E_ALL);
ini_set('display_errors', 'On');

$c = oci_connect("hr", "welcome", "localhost/XE");
if (!$c) {
    $m = oci_error();
    trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}

$sql = <<<'END'
    INSERT INTO MYTABLE(ID,MYDATE) VALUES(:ID,to_date(:MYDATE,'YYYY.MM.DD HH24:MI:SS'))
END;

$id = 1;
//$mydate = '2017.07.21 08:59:26';  // also works
$mydate = date("Y.m.d H:i:s");
echo "input value: ", $mydate, "\n\n";

$s = oci_parse($c, $sql);
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

$r = oci_bind_by_name($s, ':id', $id);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);
}

$r = oci_bind_by_name($s, ':mydate', $mydate);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not bind a parameter: '. $m['message'], E_USER_ERROR);
}

$r = oci_execute($s, OCI_NO_AUTO_COMMIT);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}


// Fetch back the data

$s = oci_parse($c, "select to_char(mydate, 'YYYY.MM.DD HH24:MI:SS') as mydate from mytable");
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}

$r = oci_execute($s, OCI_NO_AUTO_COMMIT);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}

oci_fetch_all($s, $r);
var_dump($r);

oci_rollback($c);

?>

gives this output:

input value: 2021.04.06 10:50:20

array(1) {
  ["MYDATE"]=>
  array(1) {
    [0]=>
    string(19) "2021.04.06 10:50:20"
  }
}

Things you can do:

do a var_dump() on your input date, to show its type
do a DESC on the table to show your datatype

I believe you'd added it for testing, but in production you would want to avoid running ALTER SESSION for each connection, since this adds overhead. Instead use the TO_DATE, or set the environment variables NLS_DATE_FORMAT and NLS_LANG (both are needed) before PHP starts. See the Globalization chapter in The Underground PHP and Oracle Manual

ORA-00932 inconsistent datatypes: expected DATE got NUMBER

1 Answers