Query using TO_DATE TO_CHAR failing with ORA-01858 or ORA-01843
Last updated on DECEMBER 06, 2016
Applies to:
Oracle Server - Enterprise Edition - Version 9.2.0.1 and laterInformation in this document applies to any platform.
***Checked for relevance on 02-Jul-2012***
Symptoms
An error is being generated on some clients when executing a query using TO_DATE(TO_CHAR .
The errors indicate the data is invalid for what is expected to be returned. If you add/change a query hint the error may change slightly.
You may see:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01843: not a valid month
An example of the scenario:
The DATA in SCOTT.EMP is as follows:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- ---------- --------- ---------- --------- ----- ------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8000 HAMBURGLAR THIEF 7698 03-MAR-09 100 30
SQL> alter session set NLS_DATE_FORMAT='MM/DD/YYYY';
Session altered.
SQL> alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD-hh24.mi.ss.ff';
Session altered.
SQL> select *
from scott.emp
where job='THIEF'
and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'));
select * from scott.emp where job='THIEF' and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'))
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SQL> select /*+ rule*/*
from scott.emp
where job='THIEF'
and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'));
select /*+ rule*/* from scott.emp where job='THIEF' and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'))
*
ERROR at line 1:
ORA-01843: not a valid month
Once the NLS Formats are changed back the query now executes without error:
SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';
Session altered.
SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM';
Session altered.
SQL> select *
from scott.emp
where job='THIEF'
and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'));
no rows selected
SQL> select /*+ rule*/*
from scott.emp
where job='THIEF'
and TO_DATE(TO_CHAR(HIREDATE, 'DD-MON-YY')) = TO_DATE(TO_CHAR(sysdate-1, 'DD-MON-YY'));
no rows selected
Changes
A session, instance, or database NLS parameter has changed or you may have upgraded clients.
Cause
Sign In with your My Oracle Support account |
|
Don't have a My Oracle Support account? Click to get started |
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms