ORA 01858: a non numeric character was found where a numeric was expected (4216595)

Chat now with support

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

ORA 01858: a non numeric character was found where a numeric was expected
Description

When running a Query in the Edito, one gets one of these two messages:
ORA 01858: a non numeric character was found where a numeric was expected
ORA 01850: hour must be between 0 and 23
All that's needed to replicate is having a DATE variable in the WHERE clause that's being compared to a string, for example:
select table_name, owner, last_analyzed from all_tables
where last_analyzed between '01-jan-2018' and '31-jan-2018'
order by last_analyzed desc;
The same query works in another PC using the same exact version of Toad, and the same exact version of the Oracle Client, and the same exact OS version.
Cause

When executing:
SELECT * FROM V$NLS_PARAMETERS
where UPPER(parameter) LIKE 'NLS_DATE_%';
One may see that the value of "nls_date_format" is 'HH24:MI:SS'
Resolution

TO FIX TEMPORALY, just during the current session:
alter session set nls_date_format = 'DD-MON-RR';
TO FIX PERMANENTLY:
One must set "nls_date_format" by the client with environment variables.
Additional Information

Same resolution has helped when getting "ORA-01843: not a valid month"

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title