I am having a problem when Inserting dates into a table, the odd thing is if I change to using a hard coded date instead of trying to bind it I don't get the error message anymore.

The error message is as follows ORA-01858: a non-numeric character was found where a numeric was expected, I have tried changing the format of the date and this doesn't seem to have helped.

The SQL and binds are as follows, the dates are exactly the same as printed in the working version as I have echo'ed them out. Any help would be greatly appreciated.


$strSQL = " INSERT INTO TH.TRIGGER_HDR_TH
( TRIGGER_HDR_ID
, CHILD_CLIENT_ID
, REPORT_TRIGGER_CODE
, CORRESPOND_TYPE
, CORRESPOND_EMAIL_ADDRESS
, ARCHIVE_ID
, STATUS_FLAG
, CREATED_DATE
, CREATED_USER
, START_DATE
, END_DATE
, JOB_ID
)
VALUES (
0
, -1
, 'RECURRPAY'
, 'EMAIL'
, ''
, -1
, 'GENERATED DATA'
, SYSDATE
, :INP_USER_ID
, :INP_START_DATE
, :INP_END_DATE
, :INP_JOB_ID
)";

$stmt = oci_parse($iDBConn, $strSQL);
oci_bind_by_name($stmt, ":INP_JOB_ID", $intJobId);
oci_bind_by_name($stmt, ":INP_USER_ID", $intUserId);
oci_bind_by_name($stmt, ":INP_START_DATE", $strStartDate);
oci_bind_by_name($stmt, ":INP_END_DATE", $strEndDate);



Here is the insert that actually works.



$strSQL = " INSERT INTO TH.TRIGGER_HDR_TH
( TRIGGER_HDR_ID
, CHILD_CLIENT_ID
, REPORT_TRIGGER_CODE
, CORRESPOND_TYPE
, CORRESPOND_EMAIL_ADDRESS
, ARCHIVE_ID
, STATUS_FLAG
, CREATED_DATE
, CREATED_USER
, START_DATE
, END_DATE
, JOB_ID
)
VALUES (
0
, -1
, 'RECURRPAY'
, 'EMAIL'
, ''
, -1
, 'GENERATED DATA'
, SYSDATE
, :INP_USER_ID
, TO_DATE('7/02/2010', 'DD/MM/YYYY')
, TO_DATE('26/05/2010 23:59:59', 'DD/MM/YYYY HH24:MI:SS')
, :INP_JOB_ID
)";