My Oracle Support Banner

Query using TO_DATE TO_CHAR failing with ORA-01858 or ORA-01843 (Doc ID 790098.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Server - Enterprise Edition - Version 9.2.0.1 and later
Information 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

To view full details, 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 a vibrant support community of peers and Oracle experts.