ORA-01858: A non-numeric character was found where a numeric was expected
An alpha numeric character was found when a numeric character was expected.
eg.:
SQL> select to_date('AA/10/2005', 'DD/MM/YYYY') from dual;
select to_date('AA/10/2005', 'DD/MM/YYYY') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expectedFix the input string or change the format mask in order to resolve the error.SQL> select to_date('16/10/2005', 'DD/MM/YYYY') from dual;
TO_DATE('
---------
16-OCT-05It can happen sometimes that you are expecting this error, but you still get a correct date.
eg.:
SQL> select to_date('17OCT2005','DDMMYYYY') from dual;
TO_DATE('
---------
17-OCT-05This is standard Oracle behaviour (string to date conversion rules).Oracle tries to convert the string (OCT) to a valid month before applying the format mask.
If you do not want this behaviour, use the FX formatting.
eg.:
SQL> select to_date('17OCT2005','fxDDMMYYYY') from dual;
select to_date('17OCT2005','fxDDMMYYYY') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> select to_date('17102005','fxDDMMYYYY') from dual;
TO_DATE('
---------
17-OCT-05
Welcome to our forum for Oracle error: ORA-01858 Add your own message
2 (transaction_no, customer_no,Car_No, given_date, GIVEN_TIME, type_period, payment_type, advance
_amount,miter_out)
3 values(111,11,1,'dd-May-yyyy','8:00','daily','cash' ,50,5000);
values(111,11,1,'dd-May-yyyy','8:00','daily','cash' ,50,5000)
*
???? ??? ??? ??? 3:
ORA-01858: a non-numeric character was found where a numeric was expected
WHERE ETD_EIID = 35
AND ETD_ACTV = 1
AND ETD_APRV_STAT = 4
AND TO_DATE(TO_CHAR(ETD_EFDT,'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN TO_DATE('01-Oct-2008 12:00:00 AM','DD-MM-YYYY')
AND TO_DATE('13-Oct-2008 12:00:00 AM','DD-MM-YYYY')
The Above Query is throwing following error
ERROR at line 6:
ORA-01858: a non-numeric character was found where a numeric was expected
TO_DATE('01-Oct-2008 12:00:00 AM','DD-MON-YYYY HH:MI:SS AM')
WHERE ETD_EIID = 35
AND ETD_ACTV = 1
AND ETD_APRV_STAT = 4
AND TO_DATE(TO_CHAR(ETD_EFDT,'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN TO_DATE('01-Oct-2008 12:00:00 AM','DD-MM-YYYY')
AND TO_DATE('13-Oct-2008 12:00:00 AM','DD-MM-YYYY')
The Above Query is throwing following error
ERROR at line 6:
ORA-01858: a non-numeric character was found where a numeric was expected
WHERE ETD_EIID = 35
AND ETD_ACTV = 1
AND ETD_APRV_STAT = 4
AND TO_DATE(TO_CHAR(ETD_EFDT,'DD-MM-YYYY'),'DD-MM-YYYY') BETWEEN TO_DATE('01-Oct-2008 12:00:00 AM','DD-MM-YYYY')
AND TO_DATE('13-Oct-2008 12:00:00 AM','DD-MM-YYYY')
The Above Query is throwing following error
ERROR at line 6:
ORA-01858: a non-numeric character was found where a numeric was expected
Any suggestions?
select TO_DATE('13-Oct-2008 12:00:00 AM','DD-MM-YYYY') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
select TO_DATE('13-Oct-2008 12:00:00 AM','DD-MM-YYYY HH:MI:SS AM') from dual;
TO_DATE('
---------
13-OCT-08
I have a procedure in which i pass start date and End date as a Varchar. When i try to run this procedur i got this error
pointing to WHERE date1 BETWEEN TO_CHAR (TO_DATE (pbdate,'mm/dd/yyyy HH24:MI:SS'),'mm/dd/yyyy')AND TO_CHAR (TO_DATE (pedate,'mm/dd/yyyy HH24:MI:SS'),'mm/dd/yyyy').
Please help me. Thanks in advance
If date1 is a date, there is no need for the extra to_char around the pbdate and pedate
ORA-01858: a non-numeric character was found where a numeric was expected.
There is not enough information to help you.
What is the failing statement?
Check for data, you are trying to insert some non-numeric character into a number field
SELECT A.SPID,A.LOCATION,A.TERMTYPE,A.TERMID,A.BATCHNO,A.DATETIMECLOC,A.DATETOSPC, B.PROCESS4, SUBSTR(B.PROCESS4,1,14),
TO_DATE(SUBSTR(B.PROCESS4,1,14),'DDMMYYYYHH24MISS'),
TO_DATE(SUBSTR(B.PROCESS4,1,14),'DDMMYYYYHH24MISS') - A.DATETOSPC diff_pro4,
round(TO_DATE(SUBSTR(B.PROCESS4,1,14),'DDMMYYYYHH24MISS') - A.DATETOSPC) diff_day_pro4,OPERDATE
FROM TW_13052010 A , CCHCONTROLBAT@NJ B
WHERE A.SPID = B.SPID
AND A.LOCATION = B.LOCATION
AND A.TERMTYPE = B.TERMTYPE
AND to_number(a.TERMID) = B.TERMID
AND to_number(a.BATCHNO) = B.BATCHNO
AND TO_CHAR(A.DATETIMECLOC,'DDMMYYYY') = '13052010'
AND A.SPID = '04'
please help me..i don't know where is my mistake...
From the look of it, probably it's in the data.
Most likely following conversion will fail:
TO_DATE(SUBSTR(B.PROCESS4,1,14),'DDMMYYYYHH24MISS'),
Try to check by only selecting the process4 column from the table, and check if everything matches the format mask DDMMYYYYHH24MISS
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.