oci_execute（）：ORA-01840：日期格式的输入值不够长

Question

I am currently receiving the following error message from oci in php (I've been on it for a few hours).

oci_execute(): ORA-01840: input value not long enough for date format

It strange, because when I run the query within SQL Developer it seems to work fine.

This makes me think that when I bind the parameter it is turning the dates into a type that is not able to calculate using conventional operators in oracle.

$startDateTime = '2015-03-06 00:00:00';
$endDateTime = '2015-04-06 00:00:00';
$value = '20';
$type = '$';

$SQL = "SELECT count(*) AS \"COUNT\"
               FROM bonus where value = :d_value
               AND TYPE = :d_type
               AND ((:d_valid_from between valid_from AND valid_till) OR (:d_value_till between valid_from AND valid_till) OR (:d_valid_from < valid_from AND valid_till < :d_valid_till))";

 $this->stmnt = $this->conn->prepare($SQL);
 $this->stmnt->bindParam('d_valid_from', $startDateTime);
 $this->stmnt->bindParam('d_valid_till', $endDateTime);
 $this->stmnt->bindParam('d_value', $value);
 $this->stmnt->bindParam('d_type', $type);
 $this->stmnt->execute();

I am unable to find many resources that deal with php directly with this problem in hand. Does anybody have any experience with it?

Answer 1

I think that your dates are being bound as strings in the query. Assuming that the columns you are comparing it to (e.g. valid_from) are dates, then the string value is being converted to a date using the default date format for the sessions. The difference in behavior in SQL Developer is probably because the default format is different.

Anyway, the solution is to follow a very simple and important rule, which is not to rely on default type conversion. Explicitly convert the string to a date in your query, specifying the appropriate format:

TO_DATE( :d_valid_from, 'YYYY-MM-DD HH24:MI:SS' )