Zone SQL Error When Selecting A Field With TO_DATE In The Where Clause
(Doc ID 2511464.1)
Last updated on DECEMBER 03, 2019
Applies to:Oracle Utilities Mobile Workforce Management - Version 188.8.131.52.0 and later
Information in this document applies to any platform.
On : 184.108.40.206.0 version, Database
Zone SQL error when selecting a field with TO_DATE in the where clause
currently using the M1_TASK_CHAR table to store a custom field with a date value (Date Required). you have customized the Activity Search zones to search for activities with a Date Required field equal to the input date.
It was working before but now got an error: ORA-01861: literal does not match format string
When running a simple SQL statement using SQL developer, this SQL statement works:
SELECT * FROM M1_TASK_CHAR WHERE CHAR_TYPE_CD='CM-DTREQ' AND TO_DATE(SRCH_CHAR_VAL,'YYYY-MM-DD') >= TO_DATE('2018-12-01','YYYY-MM-DD');
However, when select specified field (not *), we get the same error:
SELECT TASK_ID FROM M1_TASK_CHAR WHERE CHAR_TYPE_CD='CM-DTREQ' AND TO_DATE(SRCH_CHAR_VAL,'YYYY-MM-DD') >= TO_DATE('2018-12-01','YYYY-MM-DD');
ORA-01861: literal does not match format string
01861. 00000 - "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.
There is currently no existing base functionality that stores a date information in the M1_TASK_CHAR table or any other similar tables.
However, this can easily be replicated by created a test data in the M1_TASK_CHAR table and run the select statement.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document